Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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
See the attached file
for rolling 12 month try this
sum({<Date = {">=$(=MonthStart(Max(Date), -11))<=$(=MonthEnd(Max(Date)))"},DATE=,Year=,Month=>}Points)
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.
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.
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.
Gr8 Demo Mohit!!
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(SUM(BALANCE)<0,Num(SUM(if(KEY_RANK = 1,{<[DEFAULT_DT]={">=$(= Min(DEFAULT_DT)) < $(=Max([DEFAULT_DT]))"}>}) BALANCE,0)*(-1)/1000000000),'##,#.#0')