Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

venkey2847
New Contributor II

Rolling 12 months dynamically when month selected

 Hi All,

I have gone through all the posts but unable to achieve this please help me to get out of this issue.

I want to show rolling 12 months when month-year selected like if i select Feb-2019 i have to get  Jan-2018 to Feb-2019.

i have date format MM/DD/YYYY.

I am using following script in my qlik sense data load editor

Customer:
LOAD
CID,
Product_Name,
Amount,
Transaction_Date
// DATE(Transaction_Date,'MM/DD/YYYY') AS Transaction_Date
FROM [Mater Calendar 3.xlsx]
(ooxml, embedded labels, table is Customer);


Temp_Master_Table:
Load
Temp_Transaction_Date as Transaction_Date,
year(Temp_Transaction_Date) as Year,
'Q' & ceil(month(Temp_Transaction_Date)/3) as Quarter,
Month(Temp_Transaction_Date)as Month,
week(Temp_Transaction_Date) as Week,
Month(Temp_Transaction_Date)&'-'& year(Temp_Transaction_Date) as Month_Year1
//DATE(DATE#(MonthStart(Temp_Transaction_Date),'MMM-YYYY')) as Month_Year,
;
Load
Date(MinDate + IterNo() -1) as Temp_Transaction_Date
while (MinDate + IterNo() -1) <= Num(MaxDate);

Load
Min(DATE(DATE#(Transaction_Date,'M/D/YYYY'))) as MinDate,
Max(DATE(DATE#(Transaction_Date,'M/D/YYYY'))) as MaxDate

Resident Customer;

In Chart,  i am using following expression:

Dimension like Month-Year= SEP-2019,FEB-2019 like this my dimension have

Expression==

Sum({<Year=,Month=,Week=,Quarter=,Month_Year1=,
Transaction_Date={"$(='>=' & Date(MonthStart(addmonths(Max(Transaction_Date), -11))) & '<' & Date(Max(Transaction_Date)))"}>}
Amount)

 

Please find attached excel file one i am using for source.

Thanks In advance.

Thanks & regards,

Venkey.

 

 

 

Tags (1)
5 Replies

Re: Rolling 12 months dynamically when month selected

May be this

RangeSum(Above(Sum({<Year, Month, Week, Quarter, Month_Year1,
Transaction_Date>} Amount)
*
Avg({<Year, Month, Week, Quarter, Month_Year1,
Transaction_Date={"$(='>=' & Date(MonthStart(addmonths(Max(Transaction_Date), -11))) & '<' & Date(Max(Transaction_Date)))"}>} 1)
venkey2847
New Contributor II

Re: Rolling 12 months dynamically when month selected

Hi SUnny,
Thanks your for reply..
But it is not working fine.

Re: Rolling 12 months dynamically when month selected

Would you be able to share a qvf file where we can see what you are getting and explain what you are looking to get?

venkey2847
New Contributor II

Re: Rolling 12 months dynamically when month selected

Couldn't share app with you but whatever script i have used in data load editor same have pasted in the above comment Sunny.

source is attached excel file only

I just need one table with rolling 12 months by month.

If user select on month field it should give from past 11 months to till selected month.it should act as  dynamically.

 

Thanks,

Venkey

Re: Rolling 12 months dynamically when month selected

Unfortunately, I don't have time to build an app from the data provided... but if you are able to load the data into an app and create the chart and tell me what is wrong, I am happy to take a look at it.