8 Replies Latest reply: Jul 11, 2017 11:33 AM by SOWJANYA MOOLA

# Set Analysis: Rolling Months & 2nd condition

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.

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

Hi,

you don't close a bracket for addmonths:

regards

• ###### Re: Set Analysis: Rolling Months & 2nd condition

See the attached file

• ###### Re: Set Analysis: Rolling Months & 2nd condition

Gr8 Demo Mohit!!

• ###### Re: Set Analysis: Rolling Months & 2nd condition

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

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

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

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

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.

1. If balance =0 then do
2. If key_rank =1 then
3. Sum of all the balances for the min-max dates selected in the data.
4. 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')