topic KPI - Displaying a single static value based on a date in App Development

KPI - Displaying a single static value based on a date

Anonymous — Thu, 24 Aug 2017 20:53:57 GMT

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.

Re: KPI - Displaying a single static value based on a date

rwunderlich — Thu, 24 Aug 2017 21:12:52 GMT

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

Re: KPI - Displaying a single static value based on a date

Anonymous — Thu, 24 Aug 2017 21:24:24 GMT

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.

Re: KPI - Displaying a single static value based on a date

rwunderlich — Thu, 24 Aug 2017 21:58:05 GMT

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

Re: KPI - Displaying a single static value based on a date

Anonymous — Thu, 24 Aug 2017 22:57:13 GMT

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

Re: KPI - Displaying a single static value based on a date

Tue, 29 Aug 2017 14:02:11 GMT

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

Re: KPI - Displaying a single static value based on a date

Anonymous — Tue, 29 Aug 2017 15:41:09 GMT

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

Re: KPI - Displaying a single static value based on a date

rwunderlich — Tue, 29 Aug 2017 15:58:24 GMT

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

Re: KPI - Displaying a single static value based on a date

Anonymous — Tue, 29 Aug 2017 16:48:43 GMT

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

Re: KPI - Displaying a single static value based on a date

rwunderlich — Tue, 29 Aug 2017 17:32:34 GMT

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

Re: KPI - Displaying a single static value based on a date

Anonymous — Tue, 29 Aug 2017 18:02:26 GMT

Thanks very much. It worked. I'll need to do more reading into the differences around using single quotes versus double quotes as well general expression syntax. In addition, I tried using another basic aggregation function like Max() because I thought it should work as well. It did. I guess in this instance, because of the filters that are applied, it doesn't really matter which aggregation function you use.

Re: KPI - Displaying a single static value based on a date

rwunderlich — Tue, 29 Aug 2017 18:19:42 GMT

Glad it worked. Yes, if the Set filters down to one value of SampleField, any aggregation function -- Max, Avg, Sum, Only -- will return the same results. Technically, if you are expecting only one value and want to consider mutiple values as an error, Only() is the safest as it will return null if there is more than one value.

-Rob