Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

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.

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.

1 Solution

Accepted Solutions
Highlighted
Creator
Creator

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.

View solution in original post

8 Replies
Highlighted
Partner
Partner

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

Highlighted
Master II
Master II

See the attached file

Highlighted
Not applicable

for rolling 12 month try this

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

Highlighted
Creator
Creator

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.

Highlighted
Creator
Creator

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.

View solution in original post

Highlighted
Not applicable

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.

Highlighted
Creator III
Creator III

Gr8 Demo Mohit!!

Highlighted
Not applicable

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