1 Reply Latest reply: Nov 24, 2016 5:17 AM by Shraddha Gajare RSS

    Rolling previous 12 months monthly total in qliksense bar chart

    Chandrasheker Gompa

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