Set Analysis: Rolling Months & 2nd condition

[simongoodman]

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.

[prodanov]

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

[er_mohit]

See the attached file

[#4]

for rolling 12 month try this

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

[simongoodman]

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.

[simongoodman · Accepted solution]

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.

[#7]

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.

[bharani8]

Gr8 Demo Mohit!!

[#9]

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