Take maximum of date from if condition

[raZor]

Hi  Qlikers,

Here is the sample data I want to have a set analysis where, Sum of Amount if Source is "A" and Type Y at A's maximum date + Sum of amount if Source is "B" and Type Y at B's maximum date

Source|  Amount   |Date                |  Type

A           |     10            |  10/06/2023   |     Y

A           |        12        |   12/06/2023    |    Y

A          |       30          |   8/06/2023       |     X

B           |       27          |    11/06/2023   |  X

B         |        47        |   17/06/2023        |   Y

B          |       30          |   8/06/2023       |  Y

Here I want to have set analysis expression to show in KPI

• Sum of Amount if Source= A and Type=Y at Source 'A's  maximum date    = 12
•                                                            +
• Sum of Amount if Source= B and Type=Y at Source 'B's  maximum date    = 47

So result is 12+47 = 59

[tresesco]

=Sum(Aggr(FirstSortedValue({<Type={Y}>} Amount,-Date) ,Source))

[raZor]

Sorry, not working 

[raZor]

Can you please send the set Analysis expression for both separately

1  Sum of Amount if Source= A and Type=Y at Source 'A's maximum date = 12

                                                           +

2  Sum of Amount if Source= B and Type=Y at Source 'B's maximum date = 47

So result is 12+47 = 59

[tresesco]

The above expression works fine with me. Please check your date field values if they are text or date. If text, they need to be converted to proper date values, then it works fine. Otherwise, please share your screen shot with expression and date field values.

[raZor]

I am using

(SUM({<TYPE={"Y"}, SOURCE={"A"},Date={"$(=(max(Date)))"}>} Amount)

+

(SUM({<TYPE={"Y"}, SOURCE={"B"},Date={"$(=(max(Date)))"}>} Amount)

Here it's taking the maximum value of date but not maximum date according to the Source

[tresesco · Accepted solution]

Why do you want to write it separately for each source? What happens when you have many sources? Try to explain the real scenario so that we can suggest a better solution.

And if you still insist and may be for the learning purpose, you can try like:

(SUM({<TYPE={"Y"}, SOURCE={"A"},Date={"$(=(max({<SOURCE={"A"}>}Date)))"}>} Amount)

+

(SUM({<TYPE={"Y"}, SOURCE={"B"},Date={"$(=(max({<SOURCE={"B"}>}Date)))"}>} Amount)

[raZor]

Each source has its latest/maximum date which I require to consider in the expression and there are multiple sources with their latest date 

I want is to have is-

latest date of source of A or B  and sum of amount associated with that latest date of that source with type Y

If I write only one expression it will take only the latest or Max date of whole data not Max date for the specific source 

[NiTo]

hi,

Yes, it is taking only the maximum date of the table and not according to the source.

Regards,