N-period rolling data, moving average, easy solution.

    I found a lot of posts in qlikView community saying how hard is to make rolling data. Some of them even say that it's not even possible. I've invited quick solution for rolling data.


    Our data:


    Screen Shot 2015-04-22 at 09.42.16.png


    So we would like to calculate additional column in qlikView with rolling data...

     

    Screen Shot 2015-04-22 at 09.42.37.png

     


    ...which will be working even when we select only one month:

     

    Example solution for 3 months period with date in format YYYYMM


    1. Create additional month table:

     


    ROLLING_DATE_3M:
    LOAD
    YEAR_MONTH as YEAR_MONTH_3M_ROLLING,
    Num#(Text(Date(AddMonths(Date#(YEAR_MONTH, 'YYYYMM'),+ 1 - IterNo()), 'YYYYMM'))) as YEAR_MONTH
    Resident SALE
    while IterNo() <= 3;


    2. expression

     

    SET ROLLING_SUM = AVG({1<YEAR_MONTH_3M_ROLLING=P(YEAR_MONTH)>} SALE);

     

    and that's all. It's enough to add as diemnsion:

    YEAR_MONTH_3M_ROLLING

    and as expression:

    =$(ROLLING_SUM)

     

    Here is an example result:

     

    Screen Shot 2015-04-22 at 09.41.46.png

     

    Qvw file is attached