2 Replies Latest reply: Nov 15, 2017 1:32 PM by Mikael Strahlem RSS

    Calculated Dimension for YTD

    Mikael Strahlem

      Good afternoon,


      For the life of me I cannot figure this out, and due to my expressions being calculated fields for my list of KPI's, I am stuck using dimensions.  I'm having an issue, firstly, finding information to creating YTD as a calculated dimension (no problem for expressions), and secondly having issues trying to do it in a numeric way.


      My setup for times:

      DATE_NUMERIC                          YYYY-MM-DD

      DATE_TK                                     integer of YYYYMM

      MONTH_NAME                             MMMM



      MONTH_NUMBER_OF_YEAR         # (1-12)

      MONTH_NUMERIC                         ######  -- resembles: YYYYMM

      QUARTER_NAME                              1st Quarter, etc

      YEAR_NUMERIC                              YYYY


      These are the values from my DWH, I am not responsible for their namings nor can I change them outside of what I do in Qlik.


      I first tried to find a solution for YTD, based on dates, but have given up on that route; unless someone here has a better idea?  Also, my YTD must function dynamically since I have a listbox where the month's are selectable.  E.g. MAR_YTD would be Jan+Feb+Mar, even though values exists into october or november of that given year.


      Originally, I thought of doing a calculated dimension using MONTH_NUMERIC, and subtracting 12, since 12 will never get to a previous year, e.g. 201109 - 12 = 201097, thereby giving all of the results from JAN to SEP, but avoiding anything else in 2010.  Any help on this matter would be greatly appreciated.


      The QVD was put together specifically with spoofed data to aid in assistance.