Multiple parameters in a measure with AGGR and AVG functions

[zototo]

Hi, 

Any help is appreciated. 

I need to nest somehow in aggr function condition to count terminals only from those venues, where count_games > 0. 

I've been playing with this expression but got nothing:  

AVG(AGGR(sum({$<LOCATION_ID = {"=COUNT_GAMES>'0'"}>}COUNT_TERMINALS), Instance_Name)).

And as far as I understand, this part is totally wrong LOCATION_ID = {"=COUNT_GAMES>'0'"}. 

Thank you! 

[Clever_Anjos]

You can test this expression adding LOCATION_ID as a filter and typing =COUNT_GAMES>'0' and then hitting enter.

This will filter all LOCATION_ID where there is a field called COUNT_GAMES that is > 0 

[zototo]

Thank you for the idea, but unfortunately, it's not my case.  This filter only works for display in the app but is useless while showing a story. 

Does anyone have any ideas about inserting it into the data load editor? 

[stevedark · Accepted solution]

Hi @zototo 

Is COUNT_GAMES always a positive value (or zero)?

If so you can do it like this:

=AVG(AGGR(sum({$<LOCATION_ID=P({<COUNT_GAMES*= {">0"}>}LOCATION_ID)>}COUNT_TERMINALS), Instance_Name))

Any location which only has counts of zero will be excluded from the possible P() expression, any location with one or more rows where the count is greater than zero will be included.

As @Clever_Anjos says you want to try things out with selections to validate things work. I would also test things without the AGGR before adding that on.

Start with a table with these two expressions:

sum({$<LOCATION_ID*=P({<COUNT_GAMES*= {">0"}>}LOCATION_ID)>}COUNT_TERMINALS)

sum(COUNT_TERMINALS)

They should give different values.

Now select all values other than zero in the COUNT_GAMES field using selections, then select all possible LOCATION_ID in selections and finally clear the COUNT_GAMES selection.

This should then give you the same value in both expressions.

Then create a table with Instance_Name as a dimension with an expression of:

sum({$<LOCATION_ID*=P({<COUNT_GAMES*= {">0"}>}LOCATION_ID)>}COUNT_TERMINALS)

Set the total mode to average, and that will give you the average as the total of the table. Then implement it with the avg(aggr( function that you have and you should get the same total when Instance_Name is not the dimension.

Hope that all makes sense?

Steve

https://www.quickintelligence.co.uk/blog/

[zototo]

Thank you very much, this was exactly what was needed!😊