
1. Re: Average of Ranking based on Average
Michel Caissie May 7, 2018 9:29 AM (in response to Fabio Fantoni)Fabio,
You can get those numbers using table calculations.
If you check on the sheet validateData
1 I get the sum of the rank averages with
WINDOW_SUM( [Value (Rank)] )
where Value(Rank) is
RANK(AVG([Value]))
this is a nested calculation, so you need to set a specific computing on each
Value(Rank) computes using Category and
Value (Rank winSum) computes using Category, Day of Date Restarting every Category
2 I get the number of Days with
WINDOW_SUM( COUNTD( DATETRUNC('day', [Date]) ) )
computing on Day of Date
3So you get Value (Rank Avg) with
[Value (Rank winSum)] / [Day (Number of)]
4And to show a single value per Category, I build a filter 'keep single date' with
if index() = 1 then 1 else 0 end
computing on Day of Date
The finalView sheet is a duplicate of the validateData sheet where I
1move keep single date to the filter shelf and keep the 1s
2keep only the Value (Rank Avg) measure
3Hide the Day(Date) Header
Michel

Avg Rank Avg(mc).twbx 42.0 KB


2. Re: Average of Ranking based on Average
Fabio Fantoni May 9, 2018 7:17 AM (in response to Fabio Fantoni)Thanks a lot Michel!
Really useful