topic Rolling Period Is not working in QlikView

Rolling Period Is not working

priyarane — Wed, 25 Nov 2020 16:16:04 GMT

Hi Community,

Below expression is not showing rolling period, can some one pls suggest.

Sum(Aggr(If(Sum({<%YearMonthID={'>=$(=max(%CurrentYearMonthID)-2)<=$(=max(%CurrentYearMonthID))'}>} ED) -
Sum({<%YearMonthID={'>=$(=max(%CurrentYearMonthID)-2)<=$(=max(%CurrentYearMonthID))'}>} COLL_AMOUNT)>0,
Sum({<%YearMonthID={'>=$(=max(%CurrentYearMonthID)-2)<=$(=max(%CurrentYearMonthID))'}>} ED) -
Sum({<%YearMonthID={'>=$(=max(%CurrentYearMonthID)-2)<=$(=max(%CurrentYearMonthID))'}>} COLL_AMOUNT)),
LEV_NETTING_18))

-Priya

Re: Rolling Period Is not working

sunny_talwar — Thu, 17 Nov 2016 10:51:16 GMT

What is it doing? Giving error or the numbers are not right? Would you be able to share a sample of what you have and also provide some information as to what you are looking to get?

Re: Rolling Period Is not working

rubenmarin — Thu, 17 Nov 2016 10:51:38 GMT

Hi Priya, try adding the same set analysis to the outside Sum():

Sum({<%YearMonthID={'>=$(=max(%CurrentYearMonthID)-2)<=$(=max(%CurrentYearMonthID))'}>}

Aggr(If(Sum({<%YearMonthID={'>=$(=max(%CurrentYearMonthID)-2)<=$(=max(%CurrentYearMonthID))'}>} ED) -
Sum({<%YearMonthID={'>=$(=max(%CurrentYearMonthID)-2)<=$(=max(%CurrentYearMonthID))'}>} COLL_AMOUNT)>0,
Sum({<%YearMonthID={'>=$(=max(%CurrentYearMonthID)-2)<=$(=max(%CurrentYearMonthID))'}>} ED) -
Sum({<%YearMonthID={'>=$(=max(%CurrentYearMonthID)-2)<=$(=max(%CurrentYearMonthID))'}>} COLL_AMOUNT)),
LEV_NETTING_18))

Re: Rolling Period Is not working

priyarane — Thu, 17 Nov 2016 11:21:44 GMT

It is not showing any error, but it showing all months instead of 3

Re: Rolling Period Is not working

priyarane — Thu, 17 Nov 2016 11:22:11 GMT

values are wrong if use this

Re: Rolling Period Is not working

sunny_talwar — Thu, 17 Nov 2016 12:00:30 GMT

Is your field %YearMonthID correctly associated with your measure and dimensions? Also, %YearMonthID is formated as a number? Would you be able to share a screenshot of %YearMonthID field?

Re: Rolling Period Is not working

priyarane — Thu, 17 Nov 2016 12:53:02 GMT

all are good, Yearmonth id is like 24202,24201

Re: Rolling Period Is not working

sunny_talwar — Thu, 17 Nov 2016 12:56:31 GMT

Can you check if this gives you the right range in a text box object?

='>=' & (Max(%CurrentYearMonthID)-2) & '<=' & Max(%CurrentYearMonthID)

Re: Rolling Period Is not working

priyarane — Thu, 17 Nov 2016 13:06:34 GMT

yeah it is good, like it showing - 24200<=24202

Re: Rolling Period Is not working

sunny_talwar — Thu, 17 Nov 2016 13:10:13 GMT

Then I don't really know what the issue might be. May be if you can share a sample, I might be able to look into that

Re: Rolling Period Is not working

priyarane — Thu, 17 Nov 2016 13:31:47 GMT

Hi Sunny, we have another way which you have give me the solution before. But now it is not working. Please check on below link

Not excluding Negative values

What is the wrong in below expression which is not excluding nagative values. I want negative values should be exclude with the help of(LEV_NETTING_18={"=sum(EAD)-sum(COLL_AMOUNT)>0"}), but it is not doing.

Sum({<%YearMonthID={'>=$(=max(%CurrentYearMonthID)-2)<=$(=max(%CurrentYearMonthID))'},LEV_NETTING_18={"=sum(EAD)-sum(COLL_AMOUNT)>0"}>} EBD) -
Sum({<%YearMonthID={'>=$(=max(%CurrentYearMonthID)-2)<=$(=max(%CurrentYearMonthID))'},LEV_NETTING_18={"=sum(EAD)-sum(COLL_AMOUNT)>0"}>} COLL_AMOUNT)