Displaying the most recent months data in a KPI

[jerryr125]

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

[marpet]

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

[jerryr125]

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

[jerryr125]

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 ? 

[thi_pham]

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)

or

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