13 month rolling and YTD in same bar chart

[Anonymous]

Hi,

My requirement is to create a bar chart with dimension as Monthyear and expression as sum of Hours.

If i select Jun 2018 in Monthyear filter, then bar chart should display Jun 2017 to Jun 2018 on x axis with each bar being YTD itself.

So Jun 2017 bar will have data from Jan 2017 to Jun 2017,July 2017 bar will have Jan 2017 to July 2017 data,similarly for all bars in the chart.

I tried rangesum(above(sum(Hours),0,Monthnum)) expression but i am getting only data for jan 2018 to Jun 2018 when i select Jun 2018 in Monthyear filter. I used Monthnum as an argument of above function so that it calculates only 5 values above for May 2018 until Jan 2018 and calculates the YTD for May 2018.Similarly it will take respective Monthnum for remaining months and calculate ytd for each bar.

Also the ytd values should change according to other filters like department,location,etc.

Regards,

Jatin

[Zhandos_Shotan]

Hi, Jatin!

Recently i solved succsessfully similar calculations. It was more complex and sounds like:

'average monthly sales in same selected workdays in past 11 months' to compare with current month sales.

I simplified and adopted expression for your requirement, but not tested it.

Basic idea is to use precalculated StartDate and EndDate variables (hope you have date field). And use it in set analysys with aggregation by Monthyear.

Try something like:

SUM( {<Year=, Month=, Date=>}

  Aggr( {<Date={">=$(vStartDate) <$(vEndDate)"}>}

        Sum (Hours), Monthyear )

)

WHERE:

vMaxDate= '=Max(Date)';

vStartDate= '=MakeDate( Year(AddMonths(vMaxDate,-12)), Month(AddMonths(vMaxDate,-12)) )';

vEndDate= '=MonthStart(vMaxDate)';

This variables calculates dates depending on user selection and used in expression as precalculated values for better performance and simplyfiyng expression.

Hope this helps.

ps: Sorry, my english is not good.

[Anonymous]

Hi, this expression returns values for respective months only, what i need is each month bar should be YTD and also the chart should show last 13 months from the selected month.

Regards,

Jatin