6 Months rolling with cumulative of data

[Anonymous]

I have a requirement where i have to display the rolling 6 months in my dashboard, which i have already implemented with the below set analysis.

I have a chart, where rolling 6 months is done, from MARCH  to AUGUST

consider each month 5 employees are added. so this chart will display cumulative of previous month.

Expression i have used

(RangeSum(above( count({<Month_Year ={"$(v6Months)","$(v5Months)","$(v4Months)","$(v3Months)","$(v2Months)","$(vCurrentMonth)"}>}distinct empid),0,12)))

where variable v3Months = num#(DATE(AddMonths(max(Month_Year),-3),'MMM-YYYY'))

I have 5 employees been added in JAN and FEB also. so, i want to display those month(JAN, FEB) cumulative counts also in the chart as below so the march should have cumulative of jan and feb as 15 and so on

Help me in this...

Thanks

[pradosh_thakur]

is it possible for you to send some sample?

[Anonymous]

thanks

Yeah ill prepare and send you the sample.

Mean while, can you come up with the example i have given above.

Is that possible ?

[Anonymous]

I have attached sample.

Here i want 6 months rolling and the requirement which i have mentioned.

[pradosh_thakur]

hi kishore

PFA. Let me know if it is helpful. Use variable for last six months.

regards

Pradosh

[sunny_talwar]

May be try this

RangeSum(Above(Count(DISTINCT {<Month_Year>} empid),0,12)) *

Avg({<Month_Year = {"$(='>=' & Date(AddMonths(Max(Month_Year), -5), 'MMM-YYYY') & '<=' & Date(Max(Month_Year), 'MMM-YYYY'))"}>} 1)

[pradosh_thakur]

awesome!!!!

avg(1) is 1 for all the month. can you please make us understand the expression?

regards

Pradosh

[Anonymous]

Thank you so much sunny.

This is what, i was looking for..

Can you explain this part.

Avg({<Month_Year = {"$(='>=' & Date(AddMonths(Max(Month_Year), -5), 'MMM-YYYY') & '<=' & Date(Max(Month_Year), 'MMM-YYYY'))"}>} 1)

[sunny_talwar]

The expression is broken down into two pieces

1) RangeSum(Above(Count(DISTINCT {<Month_Year>} empid),0,12))

Finds the cumulative 12 month count.... regardless of selection in Month_Year field

2) Avg({<Month_Year = {"$(='>=' & Date(AddMonths(Max(Month_Year), -5), 'MMM-YYYY') & '<=' & Date(Max(Month_Year), 'MMM-YYYY'))"}>} 1)

Shows 1 for the last six months from the selected Month_Year and everywhere else it is 0

So, although the first expression will show value for all Month_Year regardless of selection in Month_Year, the second expression will make it 0 for anything outside the last 6 month period....

[sunny_talwar]

Just explained it above