Set Analysis for Rolling Past Two Years

[#1]

Hello QlikView folks, 

I use the following expression to calculate a rolling tow year sum of patient days. As I have several list box, I used set analysis to tell the apps to ignore the selection.

if (F_Year<=2011,
sum({$<[Event Year IMDt]= {2010,2011},[Event Month IMDt]=, [Event Month Name IMDt]=,[Source of Infection Description]=,[Severity Description]=, [Type of Infections]= >}total aggr(sum({$<[Event Year IMDt]={2010,2011} ,[Event Month IMDt]=, [Event Month Name IMDt]=, [Source of Infection Description]=,[Severity Description]=, [Type of Infections]=>}PD),[Event Year IMDt])),
sum({$<[Event Year IMDt]= {'>=$(F_Year-2)<=$(F_Year-1)'},[Event Month IMDt]=, [Event Month Name IMDt]=,[Source of Infection Description]=,[Severity Description]=, [Type of Infections]= >}total aggr(sum({$<[Event Year IMDt]={'>=$(F_Year-2)<=$(F_Year-1)'} ,[Event Month IMDt]=, [Event Month Name IMDt]=, [Source of Infection Description]=,[Severity Description]=, [Type of Infections]=>}PD),[Event Year IMDt])))

As the date range is from 2010 to current, for years from 2010 to 2011 (by checking a variable called F_Year), I used the sum of patient days from 2010 to 2011. Starting from 2011, the sum patient days should be rolling for the previous 2 years. So for example, 2012, the number should be the sum of patient days from 2010 to 2011, and for 2013, the number should be the sum of patient days from 2011 to 2012. I used the expression (highlighted in RED); however, it did not work, except for Year 2010 and 2011. The value comes up as the total of ALL the patient days vs. previous 2 year for the rolling part.

Does any one have any thought on this?

Thank you so much for your time!

[Gysbert_Wassenaar]

You won't be able to use set analysis for this. The set is calculated once for the entire chart/table, not per row. See this document for more information and a solution: Calculating rolling n-period totals, averages or other aggregations

[#3]

Thank you so much for the information. However, when I changed the expression to the following:

if

(F_Year<=2011,
sum({$<[Event Year IMDt]= {2010,2011},[Event Month IMDt]=, [Event Month Name IMDt]=,[Source of Infection Description]=,[Severity Description]=, [Type of Infections]= >}total aggr(sum({$<[Event Year IMDt]={2010,2011} ,[Event Month IMDt]=, [Event Month Name IMDt]=, [Source of Infection Description]=,[Severity Description]=, [Type of Infections]=>}PD),[Event Year IMDt])),
sum(aggr(rangesum(above(total sum({$<[Event Year IMDt]= ,[Event Month IMDt]=, [Event Month Name IMDt]=, [Source of Infection Description]=,[Severity Description]=, [Type of Infections]=>}PD),num([Event Month IMDt]),24)),[Event Month Name IMDt])))

It still does not show up the correct answer.

My tab set up the way that once it is activated, there is a default selection on the type of infections. And not facilities have infection for each month, which screwed up this set analysis. And for some reason, although I told it to ignore the selection on the 'Source of infection' and 'Severity', when I picked specific source of infection, the numbers are not correct.

Could you please provide me with the correct expression if possible?

Thank you so much!