KPI - Displaying a single static value based on a date

[Anonymous]

Hello,

I'm trying to display the latest value from a field in a KPI.

vMaxDate is declared as a variable and has the latest date from the table of information.

'Date' and 'SampleField' are both fields within my data set.

I'm trying to use the following expression within my KPI and it doesn't work.

MAX({1<Date={vMaxDate}>} SampleField)

It won't display anything. I tried copying and pasting the exact same expression into a table for debugging purposes but I receive an 'invalid dimension' error.

I wouldn't mind understanding how to display the latest value (based on date) on app startup and then the KPI displayed value being overwritten once a selection is made. I'm not sure how to approach that.

Thanks.

[rwunderlich]

If you want to display based on the latest date, and then respond to selections:

MAX({<Date={$(=max(Date))}>} SampleField)

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

[Anonymous]

Thanks for the response and your help.  I get the same behavior with your expression as I do with the one I originally posted.  Is there something I'm missing in terms of the configuration in the app?  Does it matter if the dates are out of order?  I'm not sure how to proceed.

[rwunderlich]

A couple of possibilities.

1. Confirm that your dates are stored as numbers and the max is working.  To do that, create a KPI with the expression:

Max(Date)

2. If the max works, change your KPI to

Sum({<Date={$(=max(Date))}>} SampleField)

see if you are dealing with more than one value.

-Rob

[Anonymous]

Thanks for the feedback.

1.  Tried it.  Max(Date) produces the last date within a KPI.

2.  I tried Sum() instead of Max() and I have the same result.  No value is displayed.

Michael

[#6]

Hi  Michael,

i used the following to get the max value of the selected period in a KPI object.

=max(aggr(Sum({<Country={'Europe'}>}[United States]),EndDate))

KG,

Nick

[Anonymous]

Hi Nick,

Thanks for responding.  I've tried your suggestion and it didn't work for me.  I'll need to investigate this some more.  I may have to change my user interface requirements to accommodate what I can and can't do.  If I'm unable to solve it, I may change the status of this question to close it off and remove it.

Michael

[rwunderlich]

Please post:

1. A sample of the format of "Date".

2. The results of Max(Date)

3. The results of sum(SampleField)

If possible, upload a small sample qvf.

You may also want to try:

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

-Rob

[Anonymous]

Thanks Rob.

The expression you just gave me...

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

... worked but I need to add one more modifier/filter for the city I'm interested in.  With the expression you've provided, it's adding all of the values in the SampleField on the max date and displaying it.  Once I'm able to add a filter for a single city, it should work.  I just need to try that now.  How would you recommend I add it to the expression you've provided?

Thanks again,

Michael

[rwunderlich · Accepted solution]

Sum({<Date={'$(=Date(max(Date)))'}, City={'New York'}>} SampleField)