Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for
Did you mean:
Contributor III

## Take maximum of date from if condition

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

Labels (6)

• ### Visualization

1 Solution

Accepted Solutions
MVP

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)

8 Replies
MVP

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

Contributor III
Author

Sorry, not working

Contributor III
Author

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

MVP

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.

Contributor III
Author

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

MVP

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)

Contributor III
Author

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

Creator

hi,

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

Regards,