minimum value in the last 6 months

rajinikanth — Mon, 25 Jun 2018 21:29:26 GMT

Hi All,

I have data as below attached excel file.

User has selection on date

When user selects a date say '12/mar/2017'. need to find out the minimum sales for the last 9 months from '12/mar/2017'

ie 9 months past from '12/mar/2017' is '01/july/2017'(should consider the month start for last month) and need the minimum value for the time period.

the values should be displayed for future 3 months as below

when user selects '12/mar/2017' result should be as below

min sales date range Compare date ranges

Mar-17 183 01/jul/2016 to 12/mar/2017

Apr-17 214 01/aug/2016 to 12/apr/2017

May-17 245 01/sep/2016 to 12/may/2017

Re: minimum value in the last 6 months

felipedl — Tue, 26 Jun 2018 16:01:43 GMT

HI Naidu,

Using the code

oad
*,
Date#(Year(Date)&'-'&Num(Month(Date)),'YYYY-MM') as Year_Month,
Num(Date#(Year(Date)&'-'&Num(Month(Date)),'YYYY-MM')) as Num_Year_Month;
LOAD Date, 
     sales
FROM

(ooxml, embedded labels, table is Sheet1);

And expression:

=min({<Year_Month, Num_Year_Month={">=$(=Num(AddMonths(Num_Year_Month,-8)))<=$(=Num(Year_Month))"}>}sales)

You'll get the minimum sales value for the period you selected, like below:

Attached the QVW.

topic minimum value in the last 6 months in QlikView

minimum value in the last 6 months

Re: minimum value in the last 6 months