Calculation of MTD, YTD, QTD, WTD in script level - The As-Of table concept - MTD, YTD, QTD, WTD calculation made easy

    Hi,

    I have prepared a script that will calculate the MTD, YTD, QTD, WTD without using complex set analysis using the As-Of table concept.

     

    Also from my script various types of calculations can be made like current day sale, previous day sale, current month sales, previous month sale , rolling period of N no of months without a complex set analysis and with converting a date into numbers.

     

    This is the ultimate solutions above all the posts that is related to MTD, YTD, QTD, WTD who is using complex set analysis.

     

    I have attached the sample data source and also the sample QVW file for reference and last but not the least I am very thankful to Qlik Community and their users from which I got help to prepare this script and QVW document.

    So use it and if face any problem please let me know.

     

    Use AsOfDate column as dimension and use the following expression for different purposes:

    Current Day Sale: sum({<FlagDate={'Current Day'}>}[Sales])
    Previous Day Sale: sum({<FlagDate={'Previous Day'}>}[Sales])

    Current Year Sale: sum({<FlagYear={'Current Year'}>}[Sales])
    Previous Year Sale: sum({<FlagYear={'Previous Year'}>}[Sales])

    Current Month Sale: sum({<FlagMonth={'Current Month'}>}[Sales])
    Previous Month Sale: sum({<FlagMonth={'Previous Month'}>}[Sales])

    Current Week Sales: sum({<FlagWeek={'Current Week'}>}[Sales])
    Current Week Sales: sum({<FlagWeek={'Previous Week'}>}[Sales])


    MTD Sale: sum({<FlagMTD={1}>}[Sales])
    WTD Sale: sum({<FlagWTD ={1}>}[Sales])
    YTD Sale: sum({<FlagYTD ={1}>}[Sales])
    QTD Sale: sum({<FlagQtd ={1}>}[Sales])

     

    Thanks,

    Sandip