topic Re: Set Expression - For Rolling Quarters in QlikView

Set Expression - For Rolling Quarters

md_qlikview — Thu, 04 Jun 2015 06:03:01 GMT

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.

Re: Set Expression - For Rolling Quarters

jagan — Thu, 04 Jun 2015 06:14:06 GMT

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.

Re: Set Expression - For Rolling Quarters

preminqlik — Thu, 04 Jun 2015 06:19:52 GMT

hi try this

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

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

Re: Set Expression - For Rolling Quarters

md_qlikview — Thu, 04 Jun 2015 06:28:01 GMT

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))

Re: Set Expression - For Rolling Quarters

jagan — Thu, 04 Jun 2015 07:04:46 GMT

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))

Re: Set Expression - For Rolling Quarters

linusblomberg — Thu, 04 Jun 2015 08:01:48 GMT

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

Re: Set Expression - For Rolling Quarters

md_qlikview — Thu, 04 Jun 2015 08:22:48 GMT

thanks premkumar for your reply..

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

thnks much

Re: Set Expression - For Rolling Quarters

md_qlikview — Thu, 04 Jun 2015 08:23:07 GMT

thanks linus for your reply

Re: Set Expression - For Rolling Quarters

md_qlikview — Thu, 04 Jun 2015 08:24:19 GMT

yes...this is the one

thanks much