
1. Re: Create a dynamic 'Ranking' using Window_sum or LOD
Simon Runc Sep 12, 2019 4:01 AM (in response to Conor Duke)hi Conor,
Just a quick question ...
I'm not sure I understand the expected rank, as I don't get those values if we use cumulative sales revenue/0.8

2. Re: Create a dynamic 'Ranking' using Window_sum or LOD
Conor Duke Sep 12, 2019 4:55 AM (in response to Simon Runc)Hi Simon,
That was an error on my part, the expected rank is actually 'Cum Sales / 70'.
I have updated it accordingly.
The whole idea is to get the 'Expected Rank' to eventually become less then the actual rank... then classify the difference.
Thanks for taking the time to look over my question
Regards
Conor

3. Re: Create a dynamic 'Ranking' using Window_sum or LOD
Simon Runc Sep 12, 2019 5:03 AM (in response to Conor Duke)That makes more sense!
So this is a bit complicated, as we need to use table calculations and also table calculation filters are applied last, meaning that although you filter the view (with your above/below) you are not filtering the underlying data. I've done Average, but you can follow the same method for the other stats...
First I created all your calculated fields, and created this
[Above/Below]
[Expected Rank]>[Actual Rank]
Then to get the average I did this....
[Average]
WINDOW_AVG(
if [Above/Below] then sum([Sales Revenue]) end)
So this get's the right answer, but we have it repeated for every row, as we need company in the vizLoD for the table calculations to compute as you want....
So what we need to do, is alter the average calculation like so...
[Average  single return]
if first()=0 then
WINDOW_AVG(
if [Above/Below] then sum([Sales Revenue]) end)
end
we can then move the company to the detail shelf and answer will only be populated once, against the first one it comes to (else it's null and Tableau doesn't plot Nulls)
Hope that makes some sense, and helps (btw all Table calculations are set to compute using Company)

Stats based on TC filter.twbx 15.1 KB
