topic Re: Optimize an expression in QlikView

Optimize an expression

YoussefBelloum — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Optimize an expression

avkeep01 — Wed, 30 May 2018 12:00:12 GMT

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.

Re: Optimize an expression

sunny_talwar — Wed, 30 May 2018 12:02:36 GMT

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)

Re: Optimize an expression

YoussefBelloum — Wed, 30 May 2018 12:09:32 GMT

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

Re: Optimize an expression

sunny_talwar — Wed, 30 May 2018 12:12:46 GMT

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

Re: Optimize an expression

YoussefBelloum — Wed, 30 May 2018 12:15:35 GMT

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

Thanks you

Re: Optimize an expression

sunny_talwar — Wed, 30 May 2018 12:17:30 GMT

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

Aggr(Max(Date), Customer)

Re: Optimize an expression

YoussefBelloum — Wed, 30 May 2018 12:19:10 GMT

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

Re: Optimize an expression

avkeep01 — Wed, 30 May 2018 12:22:22 GMT

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

Re: Optimize an expression

YoussefBelloum — Wed, 30 May 2018 12:24:21 GMT

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