topic Re: How to compare a single value dimension with aggr() in App Development

How to compare a single value dimension with aggr()

Anonymous — Fri, 24 Aug 2018 23:09:27 GMT

HI,

I need to find the latest product which is identified as the max product id group by season and Usage

I am able to get the latest product by Aggr(Max([product ID]),[Season],[Usage] ).

Now when printing the data on a table I need to identify the latest product so I define a dimension as

if ([product ID] = (Aggr(Max([product ID]),[Season],[Usage] ))),'True','False').

However, this always prints false even when values for the product ID and agggr(--) are the same.

Please see below the o/p for ref

Product ID	Latest?	=(Aggr(Max([Product ID]),[Season ],[Usage] ))
66319200	False	96517654
70059823	False	96517654
79841275	False	96517654
79841838	False	96517654
62691415	False	96517654
96517654	False	96517654

Appreciate your help.

Thanks,

Ravi

swuehl — Sat, 25 Aug 2018 16:29:24 GMT

Are you using this in a dimension or expression of your chart? Any other dimensions in your chart context?

andrey_krylov — Sun, 26 Aug 2018 11:49:54 GMT

Hi Ravi, try to use TOTAL aggrigation

if ([product ID] = Max(TOTAL <[Season],[Usage]> [product ID]) ,'True','False')

Anonymous — Mon, 27 Aug 2018 11:41:02 GMT

Hi Stefan,

Yes, I have a bunch of other dimensions and measures coming from different related tables (qvd files).

Thanks,

Ravi

Anonymous — Mon, 27 Aug 2018 12:27:15 GMT

Thank you so much, Andrey!

This definitely works but I am unable to use it as Dimension. I had to add that as a measure.

However, I have this filed in my filter and I don't think we can add a measure to a filter.

Is there a workaround for that. Appreciate your help!

Thanks,

Ravi

swuehl — Mon, 27 Aug 2018 18:29:39 GMT

What if you use this as calculated dimension:

= Aggr( If([product ID] = Max(TOTAL<Season, Usage> [product ID]), 'True','False'), Season, Usage, [product ID])

Anonymous — Wed, 29 Aug 2018 12:34:57 GMT

I tried but it is not working as desired. I am going to skip the filter part.

Thanks,

Ravi

itsik85 — Tue, 08 Sep 2020 15:13:06 GMT

Worked great for me! Just need some help with this formula when ignoring some field, i.e Dim1.

= Aggr( If([product ID] = Max(TOTAL <Season, Usage> {<Dim1=>} [product ID]), 'True','False'), Season, Usage, [product ID]) not working...