Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
LINKEDIN LIVE: Democratizing data to enhance customer-centricity. JULY 29TH REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
elintham
Contributor
Contributor

How to get rangesum above to ignore the dimension filter?

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

QtrRevenue $Plan $% AchievementBase SalaryCommission Earned $Commission Earned %
202001$92$91100.65%18$18102.59%
202002$177$18993.42%35$3186.84%
202003$183$19892.36%53$4584.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! 

Labels (3)
1 Solution

Accepted Solutions
sunny_talwar

Can you try this

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

View solution in original post

13 Replies
sunny_talwar

Try this

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

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?

Annotation.png

elintham
Contributor
Contributor
Author

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
Contributor
Contributor
Author

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
Contributor
Contributor
Author

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

Can you try this

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

View solution in original post