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
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
morenoju
Partner
Partner

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

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:

morenoju_0-1591807792138.png

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?)!

morenoju_1-1591807828899.png

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!

Labels (2)
1 Solution

Accepted Solutions
morenoju
Partner
Partner
Author

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

View solution in original post

3 Replies
morenoju
Partner
Partner
Author

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

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

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

View solution in original post