Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.