Two TOP filters doesn't work

Question & Answer
, ,
1

Hello Experts!

I need to filter top 10 ID’s by avg_measure for last possible date. To do that im adding filter TOP 1 days for date field and TOP 10 for ID column by avg_measure, but result is not what i expected, as i see top ID for all period, that exists in last one.
Here example:
image

In this case TOP 1 id_2 for last period (mar 3 2024) should be ID_2 = 2 with 1450 avg_value, but it shows ID_2 = 5 with avg 1300.

Here filters:

image
image688×592 16.7 KB

Is there a way to make this filters work like:

  1. filter set of data for last date
  2. find top values from that set of data

In real case Last date <> NOW and there are millions rows of data. The only way that i see is to create column on back-end IsLastDate with 0 and 1 and applied it as filter, or create parameter with single value and use it as date filter, so user can choose any 1 date and see correct data, but is there some other ways to solve this task?

2

Hi @iskorokhid

why is
grafik
if for id_2 = 2 only one value is available?

But did you try to use a rank function and filter the rank?

BR

3

Hi!
It’s aggregated value - avg(value). I tried rank but i have feeling that i was doing something wrong. Can you show me example of rank for current dataset?

I need top 1 id_2 by avg(value) in last period

4

Hello @iskorokhid and @ErikG !

@iskorokhid are you still facing this issue or were you able to find a solution? If you were able to find a solution could you share it with the community?

5

The only way that i found to solve my problem - use 1 filter top 1 date and use this limitation in chart:

image
image244×652 19.1 KB

That is not what i want, but it’s the only thing, that works for me.