3 Month Rolling

[bharatkishore]

Hi All,

stalwar1‌

I have a combo chart and Year and Month as dimensions.

I have written the following expression:

sum({<Month={'>=$(=max(Month)-2)<=$(=max(Month))'}>}aggr(rangesum(below(total sum({<Month=>}Consumption),0,3)),Month))

The expression is working fine but it is showing only value in chart. Attached image for reference.

Can you please tell me where i am doing wrong.

Thanks,

Bharat

[bharatkishore]

Thank you Sunny Bhai.. Expression is working fine. But only one problem i have. i have data till 2018 sep.. But i am getting data for 2018 Oct and 2018 Nov.. Attached image for reference:

Sep value is fine which is 709 buti am getting for Oct 2018 and Nov 2018 if we can get this it will done Sunny Bhai..

Please let me know if you need anything more.

[sunny_talwar · Accepted solution]

May be try this

=Only({<Consumption = {"*"}>} Aggr(RangeSum(Above(Sum({<Month, [Month-Year], MonthYear, Year>}Consumption), 0, 3)), [Month-Year]))

[sunny_talwar]

Or you can try this

If(Sum(Consumption) <> 0, Aggr(RangeSum(Above(Sum({<Month, [Month-Year], MonthYear, Year>}Consumption), 0, 3)), [Month-Year]))

[bharatkishore]

Thank you so much Sunny Bhai. Thanks a lot. But can you please tell me what does this do Only({<Consumption = {"*"}>}..

Sorry to take your time

[sunny_talwar]

Removing the months where Consumption isn't available

[bharatkishore]

Thank you Sunny Bhai..

[bharatkishore]

Sunny Bhai,

Today when i have done the refresh the values have become quite strange:

For jan the value is 122 and Feb it is 151 and Mar it is 185 and Jan+Feb+Mar is 458

But i am getting some strange values for 3Month rolling like for Jan it is 122, Feb 498 which i should get 273 and Mar 534 where i should get 458.

Can you please tell me where it is going wrong. Attached qvw for reference

Thanks,

Bharat

[sunny_talwar]

Have you made any changes to the sort order of Month year field in the script? Alternatively, do you have QV12 or above?

[bharatkishore]

No Sunny Bhai, i haven't made any changes regarding sorting of Month Year field.

Version i am using 12.20

[sunny_talwar]

Then try this

If(Sum(Consumption) <> 0, Aggr(RangeSum(Above(Sum({<Month, [Month-Year], MonthYear, Year>}Consumption), 0, 3)), ([Month-Year], (NUMERIC))))