current month v/s best performing month ytd in guage chart

[#1]

Hi All,

I'm trying to build a guage chart where I need to show the ratio between sales for current month and best sales month YTD .
I'm able to formulate sales for current month .
But unable to get the sales for the best performing month  .

Formula used now is as follows :

Sum( {$<Month = {$(MaxMonth)}>} sales ) / (IF(RANK(sales,Month)= 1, sales))

MaxMonth is month(max([Business Date])) .

This seems silly but appreciate if you can help to resolve best performing month part of this chart for me .

Cheers !!

[sujeetsingh]

u should not use max month but max sales month.......Just paste a sample...

[jonathandienst · Accepted solution]

Hi

To get max sales by month, just use

     Max(Aggr(Sum(sales), Month))

But if your model has more than this year's data in it, then you will need something like this (assuming you have a Year field based on Business Date):

     Max({>Year = {"$(=Max(Year))"}>} Aggr(Sum({>Year = {"$(=Max(Year))"}>} sales), Month))

Hope that helps

Jonathan

[nirav_bhimani]

Hi,

Try this expression for best performing month for ytd.

sum({<CalendarDate = {">=$(=num(Monthstart(max(CalendarDate))))<=$(=num(max(CalendarDate)))"},CalendarWeekOfYear=,CalendarQuarter=,CalendarMonthName=,CalendarYear=>}Sales)

Hope this help you.

Regards,

Nirav Bhimani

[#5]

Hi Jonathan,

It worked .

Thanks all for your help.