Max(Date) in set analysis only showing one value

[mikecherry]

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)

Thanks

Michele

[olivierrobin]

hello

I would try

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

[sunny_talwar · Accepted solution]

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]

Love you Sunny both are working !!!!

Thanks

Michele