topic Re: Max(Date) in set analysis only showing one value in QlikView

Max(Date) in set analysis only showing one value

mikecherry — Wed, 25 Apr 2018 10:24:05 GMT

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

Re: Max(Date) in set analysis only showing one value

olivierrobin — Wed, 25 Apr 2018 12:46:16 GMT

hello

I would try

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

Re: Max(Date) in set analysis only showing one value

sunny_talwar — Wed, 25 Apr 2018 12:49:04 GMT

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

Re: Max(Date) in set analysis only showing one value

sunny_talwar — Wed, 25 Apr 2018 12:53:28 GMT

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

Re: Max(Date) in set analysis only showing one value

mikecherry — Wed, 25 Apr 2018 14:54:42 GMT

Love you Sunny both are working !!!!

Thanks

Michele