How to compare a single value dimension with aggr()

[Anonymous]

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]

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

[andrey_krylov · Accepted solution]

Hi Ravi, try to use TOTAL  aggrigation

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

[Anonymous]

Hi Stefan,

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

Thanks,

Ravi

[Anonymous]

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]

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]

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

Thanks,

Ravi

[itsik85]

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...