Some assistance on Advanced Dashboards

I am in need of some help on a very advanced dashboard. I have to show a rolling 12 week pipeline of sales and quotes. I have it partially but it is just not producing what I am trying to accomplish. The data is there but I cannot produce a chart that will display it properly.

@cwdingas Can you give us a little more detail?
how are you trying to display?

I need to display our quotes by stage (Bids & Discussion) over a 12 week period in dollars and count. The chart function in the dashboard module seems very limited in what it an do and I cannot add detail to the chart or modify. I’d really like to add a drill down to the chart but that seems impossible.

I have attached a screen shot of the chart. I added some calculated fields to get me the current week and the week of the quote entry. I then have the query run a check to see if it the quote is within our 12 week period so only those display.

Thanks,

Charles

I think I have a work around but I need some BAQ help to create a calculated field that will calculate the current week and only show those quotes that go back for 12 weeks. However, it needs to roll forward. For instance, this is the 6th week of the year. I need to show it first and then week 5, 4, 3, 2, 1 and then 53, 52, back 12 weeks. Does that make sense to anyone? Can the dashboard show this or do I need to have a different tool to do this?

Make a calc field with the expression

datediff(week,OrderHed.OrderDate, Constants.Today)

Then group other fields by that.

For example:

I have three calc fields

int WeekNum = datediff(week,OrderHed.OrderDate, Constants.Today);  // GroupedBy
int OrderCount = count(OrderHed.OrderNum); 
decimal OrderTotals = sum(OrderHed.OrderAmt);

Yields:

image

(that’s from my Test DB, which hasn’t been updated since about 8 weeks ago)

1 Like

Here’s the table Criteria to get the last 12 weeks

image

You may have to get creative if you want weeks to start on a specific day of the week. Like if week 1 is this Monday through today, week 2 was last monday through the most recent sunday, etc…

Thank you. That is a huge help.

Thanks for the help Calvin. I think this will help me.