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! 

[elintham]

Hi Sunny, Thanks so much! Actually may I know what is the logic behind this expression? How does the Avg work in this expression? 

[sunny_talwar]

Avg(1) is 1 for selection and 0 for non selections. In our case we added some set analysis where we want it to be 1 and all else 0. So, basically we are trying to multiple our main expression to show value where the set analysis from Avg() meet the condition, every where else multiplying it with 0 to return 0.

Does that make sense?

[elintham]

Thanks Sunny. I get the 1 and 0 selection and non selection. But I wanted to reconfirm why you used Avg instead of other formulas. Is it because if we were to use others such as Sum or Count like Sum({<GEO = {'ASIA PACIFIC'}>}1), it would be summing up all the values for that Geo? 

[sunny_talwar]

Right. If there are 10 rows, Sum({<GEO = {'ASIA PACIFIC'}>}1) will give 10, but Avg({<GEO = {'ASIA PACIFIC'}>}1) = 1