topic Re: Displaying the most recent months data in a KPI in App Development

Displaying the most recent months data in a KPI

jerryr125 — Wed, 23 Oct 2019 07:26:39 GMT

Hi - I have quantity data an I would like to display the most recent month's data value in a KPI - once the Month/Years are selected.

I tried this - no luck:

firstsortedvalue(QuantitySold,-[SalesDate.autoCalendar.YearMonth])

thoughts ? Jerry

Re: Displaying the most recent months data in a KPI

marpet — Wed, 23 Oct 2019 08:31:15 GMT

Sum({<[SalesDate.autoCalendar.YearMonth]={"$(=Max([SalesDate.autoCalendar.YearMonth]))"}, [SalesDate.autoCalendar.Year]=, [SalesDate.autoCalendar.Month]=>}QuantitySold)

Re: Displaying the most recent months data in a KPI

jerryr125 — Wed, 23 Oct 2019 11:33:22 GMT

Hi - unfortunately that did not work.

Example - if I select the following months:

Feb 2016 : Quantity Sold 3,157

May 2016 : Quantity Sold 11,379

April 2016 :Quantity Sold 11,609

The most recent month = 11,609

It is displaying zero

thoughts ? Jerry

Re: Displaying the most recent months data in a KPI

jerryr125 — Wed, 23 Oct 2019 15:42:55 GMT

Hi again -

I have been experimenting around this the firstsortedvalue

firstsortedvalue(TOTAL([QuantitySold]),-[SalesDate])

Returns a value of 6

Which in fact is the quantity sold for last day of the month.

I need to show the total for the entire month.

How ?

Re: Displaying the most recent months data in a KPI

thi_pham — Thu, 24 Oct 2019 04:03:53 GMT

I found autocalendar don't work in set analysis and some qlik functions sometimes.

This might help:

sum({<SalesDate={">=$(=MonthStart(Max(SalesDate))) <= $(=MonthEnd(Max(SalesDate)))"}>} SalesDate)

If you want to get current month:

sum({<[SalesDate.autoCalendar.MonthsAgo]={0}>} QuantitySold)

sum(if ([SalesDate.autoCalendar.MonthsAgo] = 0 , QuantitySold, 0))