Rank function behaving weird

robindc — Sat, 16 Nov 2024 04:33:50 GMT

Hi!

Let me give some context first: In my dataset, there is a natural hierarchy between Level1,Level2 & Level3 dimensions.

What I try to do is develop a bar chart with on the one hand a variable filter to let the user choose the dimension used. Also, I provide an input box to select the top N of the dimension based on occurence in my dataset.

Previously, in another app, i've successfully used the RANK function to do so. Now, with this extra complexity on dimension level, I've written following expression:

=if('$(vCWCGranularity)'='Level1',
aggr(
if(
rank(
Count($(vNPSResponses)distinct DWH_CONTACT_EVENT_ID),4,1
)
<=vCWCTop,REASON_LEVEL_1)
,REASON_LEVEL_1),
if('$(vCWCGranularity)'='Level2',
(aggr(
if(
rank(
Count($(vNPSResponses)distinct DWH_CONTACT_EVENT_ID),4,1
)
<=vCWCTop,REASON_LEVEL_2)
,REASON_LEVEL_2
)), 100))

So based on the variable vCWCGranularity, the RNAK calculation is perfomed on a different dimension. vCWCTop defines the number of points to be shown(user input). vNPSResponses is to calculate everything over a chosen time period (this works).

When I execute this, I get weird results. The correct dimensions are displayed( for example the top 3), but with completely wrong values. When I leave out the vCWCGranularity logic and just calculate it over level1, it works fine.

is this a known bug, or is there something wrong with my logic?

Re: Rank function behaving weird

marcus_sommer — Thu, 26 Sep 2019 11:48:42 GMT

Your aggr() might not contain all needed dimensions, for example some period fields or any other category in which context the calculation should be happens.

Beside this it would be helpful (in regard of the readability of the expression and the UI performance) to use only one aggr() and to transfer the select-logic to this place. This could be done if the variable-value is identically to the used fieldnames und could be look like:

aggr(
if(rank(Count($(vNPSResponses)distinct DWH_CONTACT_EVENT_ID),4,1) <=vCWCTop,
$(vCWCGranularity))
,$(vCWCGranularity))

- Marcus

topic Rank function behaving weird in App Development

Rank function behaving weird

Re: Rank function behaving weird