topic Re: How to retrieve the value from a field corresponding to the latest date in App Development

How to retrieve the value from a field corresponding to the latest date

qlikbbguser — Fri, 23 Aug 2024 22:25:30 GMT

hello,

Here's the mockup of my data:

I wanted to

(1) create a calculation/expression to retrieve the value as of latest date based on the Date field, and put into a KPI chart as the measure.

(2) I also want the KPI box to be driven by the filter of Customer

So if I select Wholefoods in the filter, I want the 500 to be displayed in the KPI chart.

How can I achieve this? Thank you !!!!

Re: How to retrieve the value from a field corresponding to the latest date

Frank_S — Fri, 23 Aug 2024 22:49:15 GMT

Here are a couple of solutions from the community:

https://community.qlik.com/t5/New-to-Qlik-Analytics/Max-Date/td-p/1812770

https://community.qlik.com/t5/App-Development/max-value-of-max-date/td-p/1821544

'Max" reference material:

https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/Scripting/AggregationFunctions/max.htm

Kind regards...

Re: How to retrieve the value from a field corresponding to the latest date

Digvijay_Singh — Sat, 24 Aug 2024 04:00:40 GMT

May be this expression in KPI, works for me -

Max(Aggr(firstsortedvalue(Aggr(Max(Value),Date),-Date),Customer))

Re: How to retrieve the value from a field corresponding to the latest date

Digvijay_Singh — Sat, 24 Aug 2024 04:02:48 GMT

Try this also, much simpler -

Max(Total<Date> Value)

Re: How to retrieve the value from a field corresponding to the latest date

qlikbbguser — Sat, 24 Aug 2024 13:36:50 GMT

hello! Thanks very much for your help. I tried both formula you gave but they didn't work unfortunately 😞
Attaching all the fields of data - could you help investigate?

So essentially I want the latest Sales Revenue to be displayed in the KPI chart, driven by the customer name chosen in a filter. The 'latest' is determined by the Qlik Date field.

-> Based on the data in attached file, I would want to show the 250 as my KPI if I select Whole Foods in my filter.

what I tried was:

1. create an expression to convert Qlik Date field to date format, and name this new field [Qlik Date in Date Format]

2. Then i created another expression for Sales Revenue, and name this new field [Sales Revenue]:

3. Then I used your formula to calculate latest sales revenue:

My KPI chart shows '-' with customer filtered.

Re: How to retrieve the value from a field corresponding to the latest date

Frank_S — Mon, 26 Aug 2024 15:30:27 GMT

The problem appears to be with your data.

1. ensure your source data is accurate (valid date and text fields in your excel file...general is used).

2. you may not have correct associative source data. Source data may be in invalid format.

3. when you have valid source data, ensure to concatenate your fields to create monthyear date values. (not shown in uploaded sample provided.

The solution provided by @Digvijay_Singh does work if used with valid source data. His code is used in the app uploaded here.

You may use the attached QVF to reverse engineer what was done.

Should you need further assistance, you may wish to reach out to one of our trusted Qlik Partners or Qlik Professional services to troubleshoot your source data.

Kind regards...well done @Digvijay_Singh