Set Expression - For Rolling Quarters

[md_qlikview]

Hello Everyone,

I want to write set expression wherein I have to calculate total amount for Four rolling quarters, starting with the current quarter and the values should not be affected by the selection of the quarters or years on the dashboard.

I have tried below expression, by which I am getting correct values, but when I select Quarter from the filters on the dashboard the value changes with respect to the selection, which is wrong.

((Sum({<QuarterYear=,Year=,

CloseDate = {">=$(=Quarterstart(min(Date)))<=$(=QuarterEnd(min(Date),3))"}>} Amount))

Please guide me to get the required output.

Thanks in advance.

[jagan]

Hi,

Try like this for last 6 quarters, based on your requirement change the number in QuarterStart().

Sum({<QuarterYear=,Year=,

CloseDate = {">=$(=Quarterstart(max(Date), -5))<=$(=QuarterEnd(Max(Date)))"}>} Amount)

Hope this helps you.

Regards,

Jagan.

[preminqlik]

hi try this

((Sum({<Quarter=,Date=,QuarterYear=,Year=,

CloseDate = {">=$(=Quarterstart(min(Date)))<=$(=QuarterEnd(min(Date),3))"}>} Amount))

[md_qlikview]

Hi jagan,

appreciated. When i use this expression, values still varies based on selection of quarters.

But i have try below expression and now the values are freeze and correct too.

What is your thought on this.

((Sum({<QuarterYear=,Year=,

CloseDate = {">=$(=Quarterstart(date(today())))<=$(=QuarterEnd(date(today()),3))"}>} Amount))

[jagan · Accepted solution]

Hi,

Try this, put all your date related fields in the set analysis like below

((Sum({<QuarterYear=,Year=, Quarter=, Month=,

CloseDate = {">=$(=Quarterstart(date(today())))<=$(=QuarterEnd(date(today()),3))"}>} Amount))

[linusblomberg]

Look into using AsOfTables. Search for it in this forum, there is tons of information about it.

[md_qlikview]

thanks premkumar for your reply..

i did the same way i wrote earlier to jagan mohan and its working

thnks much

[md_qlikview]

thanks linus for your reply

[md_qlikview]

yes...this is the one

thanks much