Optimize an expression

[YoussefBelloum]

Hi Everybody,

I was trying to help on a thread and I found the right expression for the expected output, however, I did not find a way to optimize the expression using it's equivalent in set analysis or using the If statement inside the expression..

I want to get the Status of the Max Date By Customer and keep Customer, Date and Status as dimensions on the table

here is the tablebox (left) and the final table (right) with Exp1 (the working expression) and Exp2

Exp1: =if(Date=Max(TOTAL <Customer>  Date),only(Status)) ==> this is working

Exp2: =Only({<Date={'$(=Max(TOTAL <Customer>  Date))'}>}Status) ==> not working on the third line..

I was also trying:

Exp3: =only(if(Date=Max(TOTAL <Customer>  Date),Status)) ==> not working

I just want to know why I can't replace Exp1 using a set analysis or using the If inside the Expression..

what I'm doing wrong ?

PFA the App

Thank you

[avkeep01]

In the set analysis the calculation is done outside of the object.

$(=Max(TOTAL <Customer>  Date)

Basically like using a variable. It doesn't take the dimensions of the object in consideration. The first expression does that. So your results could be different. You should try $(=Max(TOTAL <Customer>  Date) outside the object to see which date is shown.

[sunny_talwar · Accepted solution]

You can do this....

=Only(Aggr(If(Date = Max(TOTAL <Customer>  Date), Status), Customer, Date))

but why not use FirstSortedValue here?

Didn't realize Date was one of the dimensions... may be change that to an expression... Max(Date) and then try this FirstSortedValue(Status, -Date)

[YoussefBelloum]

I don't think so, I'm using Total <Customer> so it is Grouping by the Dimension inside the table

[sunny_talwar]

Not inside set analysis. avkeep01 is right because set analysis is evaluated once per chart.... you might add TOTAL <Customer>, but <Customer> is going to be ignored because it cannot use multiple max dates based on different customers.... it will use only one max date across all customers

[YoussefBelloum]

I didn't use FirstSortedValue because I was tryng to use all the dimensions on the table (including Status)..

Thanks you

[sunny_talwar]

You can still use Date as a dimension, if you are okay with calculated dimension

Aggr(Max(Date), Customer)

[YoussefBelloum]

Sorry avkeep01 Didn't read carefully what you wrote, I forgot that I was using it inside a set analysis..

[avkeep01]

No problem. We are all here to help you. I think Sunny has the right solution for you.

[YoussefBelloum]

Thank you very much to all of you , I really appreciate !