topic Re: Set Analysis: Rolling Months & 2nd condition in QlikView

Set Analysis: Rolling Months & 2nd condition

simongoodman — Fri, 14 Jun 2013 10:55:33 GMT

I am running a test to create rolling 12 months using set analysis. I have attached an example. Pivot table 1 uses the following expression but does not work. I think it is something so obvious I am blind to it.

Sum({=$(=Date(addmonths(Max(MonthYr), -12, 'MMM-YY')) <=$(=Date(addmonths(Max(MonthYr), 0, 'MMM-YY'))"} >} Points)

I then want to add a second set analysis condition into it, found in pivot table 3. Is this possible? I would be grateful for some help. Manny thanks.

Re: Set Analysis: Rolling Months & 2nd condition

prodanov — Fri, 14 Jun 2013 11:05:52 GMT

Hi,

you don't close a bracket for addmonths:

Sum({=$(=Date(addmonths(Max(MonthYr), -12), 'MMM-YY')) <=$(=Date(addmonths(Max(MonthYr), 0), 'MMM-YY'))"} >} Points)

regards

Re: Set Analysis: Rolling Months & 2nd condition

er_mohit — Fri, 14 Jun 2013 11:27:12 GMT

See the attached file

Re: Set Analysis: Rolling Months & 2nd condition

Fri, 14 Jun 2013 11:40:49 GMT

for rolling 12 month try this

sum({<Date = {">=$(=MonthStart(Max(Date), -11))<=$(=MonthEnd(Max(Date)))"},DATE=,Year=,Month=>}Points)

Re: Set Analysis: Rolling Months & 2nd condition

simongoodman — Fri, 14 Jun 2013 13:15:39 GMT

Thanks, I have just realised a further wrinkle. The actual data model will have actual data (e.g. last month May-13) and budget data (e.g. last month Sep-13). So this solution will always return months from Sep-12 to Sep-13.

Is there a way around this? Such an amended expression and a list box where user selection determines the 12 months.

Re: Set Analysis: Rolling Months & 2nd condition

simongoodman — Sat, 15 Jun 2013 15:57:29 GMT

As a work around I modified the dataset by creating a new date field called RollMthYr applying an IF statement that distingished between actual and budget data, putting a date for the former and '-' for the latter. Then applied the suggested above set analysis replacing RollMthYr for Date.

Re: Set Analysis: Rolling Months & 2nd condition

Thu, 24 Sep 2015 20:48:21 GMT

This is a great solution, my only issue is that my data set lacks consecutive months how would i add something that would add consecutive data sets that have a 0 value.

Re: Set Analysis: Rolling Months & 2nd condition

bharani8 — Thu, 02 Jun 2016 05:42:19 GMT

Gr8 Demo Mohit!!

Re: Set Analysis: Rolling Months & 2nd condition

Tue, 11 Jul 2017 15:33:26 GMT

Hi Simon,

I have a similar problem. I need to roll up balances based on the select all dates upto current date and not only 12 months. How can I do this. I need to also incorporate a conditions to include where my balances <0 and rank = 1, hten select all the dates and sum my balances. can you please explain how to write the syntax. Below is what I wrote and I'm getting syntax error. Your help is really appreciated. can I write set analysis with IF condition? Sorry I'm new to Qlikview.

If balance =0 then do
If key_rank =1 then
Sum of all the balances for the min-max dates selected in the data.
Then finally converting the balance in billions. That is why dividing by billion.

=if(SUM(BALANCE)<0,Num(SUM(if(KEY_RANK = 1,{<[DEFAULT_DT]={">=$(= Min(DEFAULT_DT)) < $(=Max([DEFAULT_DT]))"}>}) BALANCE,0)*(-1)/1000000000),'##,#.#0')