topic Re: 6 Month Rolling Within 12 Month Rolling in App Development

6 Month Rolling Within 12 Month Rolling

Anonymous — Sat, 16 Nov 2024 09:47:32 GMT

Hi there,

I am working on a bar chart that counts a number of issues raised and shows it for the last 12 months (starting from the previous month). So for example, this month is May 2017; this means the bar chart will show from April 2017, going back to May 2016.

Within each bar representing the months, I want to see the count of issues for the past 6 months - April 2017 should show a total of the count of issues for April, March, February, January, December, November. and then the same pattern for the other 11 months.

So far my set analysis is as follows to achieve the 12 month rolling (Static) chart:

COUNT(DISTINCT{1<NM_IncEventDate={'>=$(=MonthStart(AddMonths(Max(NM_IncEventDate),-12))) <=$(=MonthEnd(AddMonths(Max(NM_IncEventDate),-1)))'}>}NM_ID)

This only counts for each individual month, so how do I get each month within this 12 month range, to total up the last 6 months performance?

I have also attached a photo of the graph so far, below:

Thank you

Re: 6 Month Rolling Within 12 Month Rolling

sunny_talwar — Fri, 12 May 2017 11:20:15 GMT

May be this

RangeSum(Above(

Count(DISTINCT {1<NM_IncEventDate = {'>=$(=MonthStart(AddMonths(Max(NM_IncEventDate),-12))) <=$(=MonthEnd(AddMonths(Max(NM_IncEventDate),-1)))'}>} NM_ID), 0, 6))

or you can use The As-Of Table‌ approach

Re: 6 Month Rolling Within 12 Month Rolling

sunny_talwar — Fri, 12 May 2017 11:21:56 GMT

or maybe this:

RangeSum(Above(

Count(DISTINCT {1} NM_ID)

, 0, 6)) * Avg({1<NM_IncEventDate = {'>=$(=MonthStart(AddMonths(Max(NM_IncEventDate),-12))) <=$(=MonthEnd(AddMonths(Max(NM_IncEventDate),-1)))'}>} 1)

Re: 6 Month Rolling Within 12 Month Rolling

Anonymous — Fri, 12 May 2017 11:47:42 GMT

Hi Sunny,

thank you for the reply.

Your first suggestion seemed to work better than the second as it kept it to a 12 month period. However it doesn't seem to be produced the correct numbers. This is how the graph now looks with your first suggestion:

Thanks

Re: 6 Month Rolling Within 12 Month Rolling

sunny_talwar — Fri, 12 May 2017 11:53:50 GMT

I see there are two dimensions in your chart, do you mind sharing the names of your dimensions? Also, would it be possible to share a sample of your qvf file? Might be easier to look at it and test out few things?

Re: 6 Month Rolling Within 12 Month Rolling

Anonymous — Mon, 15 May 2017 07:21:40 GMT

Hi Sunny,

The two dimensions are MonthYear and IncidentType. MonthYear is a Time Dimension table field, much like the master calendar but one I made.

Unfortunately I cannot share the qvf as the app holds real data. Apologies for this.

Re: 6 Month Rolling Within 12 Month Rolling

Anonymous — Tue, 16 May 2017 07:26:49 GMT

Sunny,

An issue with trying to achieve this way having two dimensions so I had to remove one of them. I kept the date dimension.

I have a calculation that almost works now:

sum(aggr(RangeSum(above(

Count(DISTINCT {1<NM_IncEventDate = {'>=$(=MonthStart(AddMonths(Max(NM_IncEventDate),-12))) <=$(=MonthEnd(AddMonths(Max(NM_IncEventDate),-1)))'}>} NM_ID), 0, 6)),MonthYear))

However, because the range is filtered down to 12 months, it cannot reach out to previous months to get the last 6 months performance. An example of this is May 2016 cannot see Nov. 2015 - Apr. 2016 because the range is May 2016 - Apr. 2017.

Re: 6 Month Rolling Within 12 Month Rolling

sunny_talwar — Fri, 19 May 2017 11:33:51 GMT

What if you do this?

Sum({1<NM_IncEventDate = {'>=$(=MonthStart(AddMonths(Max(NM_IncEventDate),-12))) <=$(=MonthEnd(AddMonths(Max(NM_IncEventDate),-1)))'}>} Aggr(RangeSum(Above(Count({1} DISTINCT NM_ID), 0, 6)), MonthYear))

Re: 6 Month Rolling Within 12 Month Rolling

Anonymous — Fri, 19 May 2017 11:47:09 GMT

That's it, excellent! Works perfectly.

Thanks for the help!

Re: 6 Month Rolling Within 12 Month Rolling

sunny_talwar — Fri, 19 May 2017 11:49:21 GMT

If the above worked, this should have also worked (unless you have changed the sort)

RangeSum(Above(Count(DISTINCT {1} NM_ID), 0, 6))

Avg({1<NM_IncEventDate = {'>=$(=MonthStart(AddMonths(Max(NM_IncEventDate),-12))) <=$(=MonthEnd(AddMonths(Max(NM_IncEventDate),-1)))'}>} 1)

This is slightly better because this doesn't use Aggr() function... but its upto you to give it a shot

Re: 6 Month Rolling Within 12 Month Rolling

aamirSiddiquee1 — Thu, 27 Feb 2020 14:08:55 GMT

Please use the following Set analysis for your work-

Sum({1<NM_IncEventDate = {'>=$(=MonthStart(AddMonths(Max(NM_IncEventDate),-12))) <=$(=MonthEnd(AddMonths(Max(NM_IncEventDate),-1)))'}>} Aggr(RangeSum(Above(Count({1} DISTINCT NM_ID), 0, 6)), MonthYear))