Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

gompa786
Not applicable

Rolling previous 12 months monthly total in qliksense bar chart

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..

1 Reply
shraddha_g
Not applicable

Re: Rolling previous 12 months monthly total in qliksense bar chart