
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Subscribe by Topic:
-
Chart
-
Data Load Editor
-
expression
-
Script
-
Set Analysis
-
Visualization
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
=Sum(Aggr(FirstSortedValue({<Type={Y}>} Amount,-Date) ,Source))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sorry, not working

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
hi,
Yes, it is taking only the maximum date of the table and not according to the source.
Regards,
