KPI for the latest date using set analysis with If function

[odassier]

Hello fellow Qlikers,

I wonder if someone can help me with this as nothing I've tried seems to work (I get either "0.0%" or "-" each time.

I have two dimension filters, one called "Index" the other "Spread" and daily data for the spreads for all the indices in my app.

When I chose a line chart, I use this expression which works fine: 

if(Index='CSI-300' and Spread='SH vs MH',[CSI-300_risk_spreads.SHF-MHF],
if(Index='CSI-300' and Spread='SH-S vs SH',[CSI-300_risk_spreads.SHS-SHF],
if(Index='CSI-300' and Spread='MH-S vs MH',[CSI-300_risk_spreads.MHS-MHF],
if(Index='CSI-300' and Spread='SH-S vs MH-S',[CSI-300_risk_spreads.SHS-MHS]))))

the line chart changes depending on which index I select, or which spread and shows me the data for the date selection I chose in the date filter.

Now I want to have a KPI object above the chart showing the latest value for the selected spread.  So, I thought of using set analysis like this:

if(Index='CSI-300' and Spread='SH vs MH',Sum({<Date={'$(=max(Date))'}>}[CSI-300_risk_spreads.SHF-MHF]),
if(Index='CSI-300' and Spread='SH-S vs SH',Sum({<Date={'$(=max(Date))'}>}[CSI-300_risk_spreads.SHS-SHF]),
if(Index='CSI-300' and Spread='MH-S vs MH',Sum({<Date={'$(=max(Date))'}>}[CSI-300_risk_spreads.MHS-MHF]),
if(Index='CSI-300' and Spread='SH-S vs MH-S',Sum({<Date={'$(=max(Date))'}>}[CSI-300_risk_spreads.SHS-MHS])))))

But it gives me "0.0%"

Any idea what I am doing wrong in this expression?

Thanks for your help.

Olivier

[jonathandienst]

Perhaps there is no data at the maximum possible value of Date. Does Date have a time component?

Alternatively, you may need to add a Date() format inside the set expression. if the Date field is formatted with the default Date format:

Sum({<Date={'$(=Date(max(Date)))'}>}....

or

Sum({<Date={"$(=Date(max(Date)), 'YYYY/MM/DD')"}>}....

if the date format needs to be (for example) yyyy/mm/dd.

[odassier]

Hi Jontydkpi,

Thanks for helping.  I checked that there is indeed data for the max date (for all the indices). My date follows the usual format (i.e. I am using the autoCalendar data in other places and it works fine).  I tried replacing my set analysis expression with your but got the same results (i.e. "0.0%" instead of the "1.6%" I'm looking for and which shows up in other places (i.e. tables and charts).  It seems it is the set analysis with the if function that might be the problem?

Olivier

[jensmunnichs]

Have you checked to see if max(Date) returns the date you're expecting? Because if you've got a calendar that covers, for example, the entirety of 2018, max(Date) might be 31-12-2018, even though max(Date) in your data table would be something like 4-12-2018. In that case the sum for max(Date) would be 0.0%.

EDIT: Actually, have you tried {"$(=max(Date))"} (double quotes instead of single quotes)?

[odassier]

Hi Jens,

Yes, Max(Date) does return Nov 30th as expected and I tried with double quotes as you suggested but got the same result (i.e. "0.0%" instead of "1.6%" for that date.).  IN fact, I get "0.0%" even if I chose an earlier date (e.g. max(date)-5").

Cheers,

[odassier]

Okay, I found an old post with a solution by Sunny (https://community.qlik.com/t5/New-to-QlikView/Display-latest-value-using-last-date/m-p/8450#M2080) that seems to work for this case.

if(Index='CSI-300' and Spread='SH vs MH',FirstSortedValue([CSI-300_risk_spreads.SHF-MHF], -Date),
if(Index='CSI-300' and Spread='SH-S vs SH',FirstSortedValue([CSI-300_risk_spreads.SHS-SHF], -Date),
if(Index='CSI-300' and Spread='MH-S vs MH',FirstSortedValue([CSI-300_risk_spreads.MHS-MHF], -Date),
if(Index='CSI-300' and Spread='SH-S vs MH-S',FirstSortedValue([CSI-300_risk_spreads.SHS-MHS], -Date)))))

For now this works but let me know if there is a more elegant solution using set analysis.

Olivier