Average of Top K in a straight table - values change when I click on the dimension!

[morenoju]

Hi all,

I'm quite confused with the following table:

I have a dimension called calendar_hour and a measure defined as the avg prob of the top-12 segments with max prob:

Avg({<segment_id={'=Rank(TOTAL max({1<calendar_date = $::calendar_date,calendar_hour= $::calendar_hour>}prob)) <=12'},segment_name=>}prob)

The user selects a calendar_date and the table displays the following values:

However, if the user picks one calendar hour... the result changes (didn't we say that for calendar_hour=1 the result was 0.09?)!

I'm pretty sure 0.54 is the actual correct value. How can I change the measure so the table displays these values directly and they are consistent when users pick a calendar_hour?

Thanks!

[morenoju]

I may have found what it's faling, even if I don't know yet how to solve it...

Seems like the Top-12 is being evaluated before checking the value of calendar_hour. The problem is how to modify the measure so it takes into account that each calendar_hour may have a different top-12 of segments.

[morenoju]

I read in a @swuehl post:

The set analysis is restricting the record set internally used for your chart before the chart's cube is actually built and aggregation functions are evaluated, hence it also limits the dimensional values by default (just like making manual selections).

If you are using a Sum(If()) instead, the internally used record set is not affected, the aggregation function will loop over all records and then filter by the If() condition, which values to consider for aggregation.

So I'm now trying to use an Avg(If()) like

Avg(Aggr(if(Rank(TOTAL max({1<calendar_date = $::calendar_date,calendar_hour= $::calendar_hour>}prob))<=12,avg(prob)),segment_id))

The idea is to say for each segment_id, if it's part of the Top-12 take the avg(prob) and at the end, make the avg of all avg(prob) of the top-12 segments. Not working yet though. 

[morenoju · Accepted solution]

I proposed an alternative to this and it was accepted. I will no longer implement this feature the way it had been described.