Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Experts,
I am having difficulty in creating a rolling previous 12 month monthly totals in qlik sense using bar chart...
I have a rolling month filter and when I choose October it gives be only 3 bars with october16, No16, Dec16..
The script I have used to build the rolling periods logic is as below:
Expenses_QVD:
LOAD
"Year",
"Month",
"MonthName",
Quarter,
Version,
//Periods,
Date(Date#(Periods,'YYYYMM'),'MMM YY') as Periods,
"COST CENTRE",
SumOfBudget_Amount,
"Comm_Per 1",
"Comm_Per 2",
"Comm_Per 3",
"Comm_Per 4",
"Comm_Per 5",
Blank1,
Blank2,
Blank3,
AccGroup1,
AccGroup2,
AccGroup3,
AccGroup4,
AccGroup5,
AccGroup6,
AccGroup7,
Cost_Element
FROM [lib://QS_Cost_Center_QVD (opd_f29121)/Business_Expenses.qvd](qvd)
//where match(Year,'2016');
where match(Year,'2016');
//set vMaxYear = '2016';
//let vMaxYear = max(Periods);
RollingMonths:
load Month, Addmonths(Periods,-12,'YYYYMM') as RolledMonth, Year(Addmonths(Periods,-12)) as RolledPeriod
resident Expenses_QVD;
load Month, Addmonths(Periods,-11,'YYYYMM') as RolledMonth, Year(Addmonths(Periods,-11)) as RolledPeriod
resident Expenses_QVD;
load Month, Addmonths(Periods,-10,'YYYYMM') as RolledMonth, Year(Addmonths(Periods,-10)) as RolledPeriod
resident Expenses_QVD;
load Month, Addmonths(Periods,-9,'YYYYMM') as RolledMonth, Year(Addmonths(Periods,-9)) as RolledPeriod
resident Expenses_QVD;
load Month, Addmonths(Periods,-8,'YYYYMM') as RolledMonth, Year(Addmonths(Periods,-8)) as RolledPeriod
resident Expenses_QVD;
load Month, Addmonths(Periods,-7,'YYYYMM') as RolledMonth, Year(Addmonths(Periods,-7)) as RolledPeriod
resident Expenses_QVD;
load Month, Addmonths(Periods,-6,'YYYYMM') as RolledMonth, Year(Addmonths(Periods,-6)) as RolledPeriod
resident Expenses_QVD;
load Month, Addmonths(Periods,-5,'YYYYMM') as RolledMonth, Year(Addmonths(Periods,-5)) as RolledPeriod
resident Expenses_QVD;
load Month, Addmonths(Periods,-4,'YYYYMM') as RolledMonth, Year(Addmonths(Periods,-4)) as RolledPeriod
resident Expenses_QVD;
load Month, Addmonths(Periods,-3,'YYYYMM') as RolledMonth, Year(Addmonths(Periods,-3)) as RolledPeriod
resident Expenses_QVD;
load Month, Addmonths(Periods,-2,'YYYYMM') as RolledMonth, Year(Addmonths(Periods,-2)) as RolledPeriod
resident Expenses_QVD;
load Month, Addmonths(Periods,-1,'YYYYMM') as RolledMonth, Year(Addmonths(Periods,-1)) as RolledPeriod
resident Expenses_QVD;
load Month, Periods as RolledMonth, Year(Periods) as RolledPeriod
resident Expenses_QVD;
---------------------------------------------------------------------------------------------------------------------------------
Expression for the measure is : sum(aggr( rangesum( above( sum(SumOfBudget_Amount),0,1) ),Month)) /1000000
Please advise if somebody has done rolling totals using a bar chart in qliksense..