topic Re: 3 Month Rolling in QlikView

3 Month Rolling

bharatkishore — Wed, 31 Oct 2018 11:16:51 GMT

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

Re: 3 Month Rolling

sunny_talwar — Wed, 31 Oct 2018 11:19:49 GMT

1) Have you made selection in year field?

2) What makes you say that the expression is working fine? Is the number that shows up for 2012 is the number you expect to see?

3) Do you expect to see all the years? or what is wrong here?

Re: 3 Month Rolling

bharatkishore — Wed, 31 Oct 2018 11:28:38 GMT

Sunny Bhai if i made any selection in year and month the value is showing correct.

I mean if i select any year for example 2012 and Mar month the value is showing fine.

But when i don't filter anything i am getting 2012 dec month by default..

I would like to show last 3 months by default and when i filter based on year and month the graph should change.

Please let me know if you need anything Sunny Bhai..

Re: 3 Month Rolling

sunny_talwar — Wed, 31 Oct 2018 11:31:38 GMT

I think you might need to use MonthYear field in your aggr function... something like this

Sum({<Month = {">=$(=max(Month)-2)<=$(=max(Month))"}>} Aggr(RangeSum(Below(TOTAL Sum({<Month>} Consumption), 0, 3)), MonthYearField))

Re: 3 Month Rolling

bharatkishore — Wed, 31 Oct 2018 11:44:39 GMT

Now i am getting for all months one one value Sunny Bhai.

Because of application size i am keeping for one year.

Request you to find the attached file and let me know what i need to do

Thanks

Re: 3 Month Rolling

sunny_talwar — Wed, 31 Oct 2018 11:45:52 GMT

Would you be able to also let us know what needs to be the output based on selection and without selection?

Re: 3 Month Rolling

bharatkishore — Wed, 31 Oct 2018 11:51:18 GMT

Sunny Bhai,

By default it should show last 3 months rolling without selection.

And if i select any month and month or year based on those selections the graphs should change. And all the time only 3 graphs should be there.

Please let me know if you need anything more.

Re: 3 Month Rolling

sunny_talwar — Wed, 31 Oct 2018 11:53:50 GMT

Make sense, but what does this translate to in numbers based on the sample provided

Re: 3 Month Rolling

bharatkishore — Fri, 02 Nov 2018 11:20:38 GMT

Sunny Bhai.. Please find the attached qvw.

Here i have two years of data 2012 and 2013

now in 2012 Jan value is 120 and Feb value is 148 and Mar value is 180.

Now in 2012(whether it is selected or even though if it is not selectd) the values should be Jan value as 120 and feb value as Jan+Feb 120+148=268 and Mar value should be i.e Jan+Feb+Mar 448 and for april it should be Feb+Mar+april= 489 and the same applies till December.

Now we have 2013 and 2013 jan value will be 2012 Nov 170 + 2012 Dec 172+ 2013 Jan 163 i.e 505 i need to get for 2013 Jan and same goes till rest for all the months.

Kindly check the attached qvw and let me know anything if you need Sunny Bhai

Thanks,

Bharat

Re: 3 Month Rolling

sunny_talwar — Fri, 02 Nov 2018 11:32:48 GMT

Try this

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

Re: 3 Month Rolling

bharatkishore — Fri, 02 Nov 2018 11:50:34 GMT

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.

Re: 3 Month Rolling

sunny_talwar — Fri, 02 Nov 2018 11:54:32 GMT

May be try this

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

Re: 3 Month Rolling

sunny_talwar — Fri, 02 Nov 2018 11:55:48 GMT

Or you can try this

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

Re: 3 Month Rolling

bharatkishore — Fri, 02 Nov 2018 11:59:44 GMT

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

Re: 3 Month Rolling

sunny_talwar — Fri, 02 Nov 2018 12:05:04 GMT

Removing the months where Consumption isn't available

Re: 3 Month Rolling

bharatkishore — Fri, 02 Nov 2018 12:14:54 GMT

Thank you Sunny Bhai..

Re: 3 Month Rolling

bharatkishore — Mon, 05 Nov 2018 13:38:59 GMT

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

Re: 3 Month Rolling

sunny_talwar — Mon, 05 Nov 2018 13:42:32 GMT

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

Re: 3 Month Rolling

bharatkishore — Mon, 05 Nov 2018 13:49:47 GMT

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

Version i am using 12.20

Re: 3 Month Rolling

sunny_talwar — Mon, 05 Nov 2018 13:51:53 GMT

Then try this

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