Skip to main content
Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
mikecherry
Contributor III
Contributor III

Max(Date) in set analysis only showing one value

Hi all,

I have this table in qlikview (see below) and I want to show the  value based on the max(date) available for all my dimensions.

At the moment the problem is that it's actually showing the value following the max(date) available but it's not working for all the "PortfolioDealName" but only for one.


Can anyone help me?

This is the expression I am currently using: sum({<TransactionTypeName={"Unfunded reconciliation (memo)"},EffectiveDate={"$(=Date(Max(EffectiveDate), 'YYYY-MM-DD'))"}>}TransactionCcyAmt)

Qlikview Capture.JPG

Thanks

Michele

1 Solution

Accepted Solutions
sunny_talwar

Set analysis is evaluated once per chart... so since the max date is 19-04-2018 across all PortfolioDealName, that is what you are seeing... try using FirstSortedValue may be

FirstSortedValue({<TransactionTypeName={"Unfunded reconciliation (memo)"}>}Aggr(Sum({<TransactionTypeName={"Unfunded reconciliation (memo)"}>}TransactionCcyAmt), PortfolioDealName, LegalEntityName, EffectiveDate), -Aggr(Only({<TransactionTypeName={"Unfunded reconciliation (memo)"}>} EffectiveDate), PortfolioDealName, LegalEntityName, EffectiveDate))

View solution in original post

4 Replies
olivierrobin
Specialist III
Specialist III

hello

I would try

1(=Date(Max(EffectiveDate)  instead of $(=Date(Max(EffectiveDate),    (replace $ par 1 meaning ignore all sélections)

sunny_talwar

Set analysis is evaluated once per chart... so since the max date is 19-04-2018 across all PortfolioDealName, that is what you are seeing... try using FirstSortedValue may be

FirstSortedValue({<TransactionTypeName={"Unfunded reconciliation (memo)"}>}Aggr(Sum({<TransactionTypeName={"Unfunded reconciliation (memo)"}>}TransactionCcyAmt), PortfolioDealName, LegalEntityName, EffectiveDate), -Aggr(Only({<TransactionTypeName={"Unfunded reconciliation (memo)"}>} EffectiveDate), PortfolioDealName, LegalEntityName, EffectiveDate))

sunny_talwar

Alternatively, you can do this

Sum(<TransactionTypeName = {"Unfunded reconciliation (memo)"}>}Aggr(If(EffectiveDate = Max(<TransactionTypeName = {"Unfunded reconciliation (memo)"}>}TOTAL <LegalEntityName, PortfolioDealName> EffectiveDate), Sum(<TransactionTypeName = {"Unfunded reconciliation (memo)"}>} TransactionCcyAmt)), PortfolioDealName, LegalEntityName, EffectiveDate))

mikecherry
Contributor III
Contributor III
Author

Love you Sunny both are working !!!!

Thanks

Michele