Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
raZor
Contributor III
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)
1 Solution

Accepted Solutions
tresesco
MVP
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)

View solution in original post

8 Replies
tresesco
MVP
MVP

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

raZor
Contributor III
Contributor III
Author

Sorry, not working 

raZor
Contributor III
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

tresesco
MVP
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.

raZor
Contributor III
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

 

tresesco
MVP
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)

raZor
Contributor III
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 

NiTo
Creator
Creator

hi,

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

Regards,