minimum value in the last 6 months

[rajinikanth]

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

[felipedl]

HI Naidu,

Using the code

l

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.