How to get rangesum above to ignore the dimension filter?

[elintham]

Hi Qlik Community, 

I have a straight table with one dimension (either Qtr or Week) and multiple measures like the below table example. 

The dimension is showing until vLastWeek. The measures Revenue $ & Plan $ are rolling sum by week using rangesum above expressions. % Achievement is Revenue$/Plan$. Base Salary is fixed. Commission Earned $ is conditional and dependent on the % Achievement. Commission Earned % is Commission Earned $/Base Salary. So far with these formulas, i'm getting the results that I'd like. But because I'm using a rangesum above formula for Revenue$ & Plan$, I am not able to filter the qtr without the numbers running. Is there a way I can change the formula to ignore the filter on the dimensions? 

Revenue $: Rangesum(Above(Sum({<GEO={'AMERICAS'}>}DOLLARS),0,Rowno()))

Qtr	Revenue $	Plan $	% Achievement	Base Salary	Commission Earned $	Commission Earned %
202001	$92	$91	100.65%	18	$18	102.59%
202002	$177	$189	93.42%	35	$31	86.84%
202003	$183	$198	92.36%	53	$45	84.72%

Hi @sunny_talwar , will you be able to help with this? I saw a few posts on this for charts but not sure how to apply it to a straight table. I need the dimensions to be able to be filtered as I'm also doing a report on this via NPrinting and need the weeks/qtrs to be filtered for certain pages. 

Thanks in advanced! 

[sunny_talwar]

Try this

RangeSum(Above(Sum({<GEO = {'AMERICAS'}, Qtr, Week>}DOLLARS), 0, Rowno()))
*
Avg({<GEO = {'AMERICAS'}>} 1)

[elintham]

Hi Sunny,

I've tried, but the number runs following my filter. For example, if I only filter QTR = 202002, then it shows the Revenue Sum for only QTR = 202002 instead of Rangesum from QTR 202001. 

[sunny_talwar]

Are you making selection in Qtr field or are you making selection in another Qtr related field? If you are making selection in Qtr field... did you make sure to ignore selection in Qtr field in your RangeSum Expression?

[elintham]

Hi Sunny, 

I used the expression you gave, my QTR dimension is using this formula =if(WEEK<$(vThisWeek), QTR) and I'm filtering that field. 

[sunny_talwar]

Can you convert your calculated dimension into set analysis within your expression?

[elintham]

Hi Sunny, I moved the vLastWeek filter to be part of the Measure as such: 

RangeSum(Above(Sum({<GEO = {'AMERICAS'}, WEEK={"<=$(=vLastWeek)"}, QTR>}CR_DOLLARS), 0, Rowno())) * Avg({<GEO = {'AMERICAS'}>} 1)

and left the dimension as is, purely QTR and the expression works. But I have a question, does this only work when the dimension is as it is? And when I filter the QTR, it still shows the rest of the quarters in the straight table, just that there are no numbers and the values are grey-ed out. Is this what its supposed to show? 

[sunny_talwar]

You can remove  grey-ed out stuff by unchecking Show zero value under add-on/Data handling.

[elintham]

Hi Sunny, Thanks it works! How about if I only want to show only qtrs to date in the table. For example based on our fiscal calendar we are in Q3 right now, so the table would only rangesum until previous week and only display until QTR = 202003. Previously we had the if condition within the dimension and it only shows until 202003, but without it, it is showing all four qtrs until 202004. I've tried to add a QTR filter into the measure, but it is still showing until QTR = 202004. Is it because of the rangesum formula?

RangeSum(Above(Sum({<GEO = {'AMERICAS'}, WEEK={"<=$(=vLastWeek)"}, QTR={"<=$(=vThisQTR+200000)"}>}DOLLARS), 0, Rowno())) * Avg({<GEO = {'AMERICAS'}>} 1)

[sunny_talwar · Accepted solution]

Can you try this

RangeSum(Above(Sum({<GEO = {'AMERICAS'}, WEEK, QTR>}DOLLARS), 0, Rowno()))
*
Avg({<GEO = {'AMERICAS'}, WEEK={"<=$(=vLastWeek)"}, QTR={"<=$(=vThisQTR+200000)"}>} 1)