0 Replies Latest reply: Jun 15, 2016 11:59 AM by Svein Gundhus RSS

    line chart with manipulated dimention and conditional measure

    Svein Gundhus

      Hi

      This is my first post in the Qlik community.

       

      My dimension is calculating historical SUMs on flagged dates and adds yesterday as the last data point.

       

      First part of my dimension shows sum(MyVariableToSum) last day of month three years back based on flagged last day in month (vYearMonth). Second part of my dimension shows sum today:

       

      Combined:

      DATE(IF(MonthName(addmonths(Dato,0))= MonthName(addmonths(Today(),0)), DATE(Today() -1 ), vYearMonth))

      Result is returning the first dates of months three year back and yesterday’s date at the end on the horizontal axis. This is what I wish to accomplish.

       

      Now I need to create a conditional measure.

      My goal is to return sum each last day of month for historical data AND sum(Today -1) as the last data point. I’m thinking a conditional If would do this.

       

      This works for historical data (Dato is my date field) and sums each flagged [MonthEndDt]:

      1) sum({<MonthEndDt={'-1'}>}MyVariableToSum)

       

      This works for yesterday’s data (Dato is my date variable):

      2) sum({<Dato={">=$(=date(today()-1,'DD.MM.YYYY'))"}>}MyVariableToSum)

       

      Combining this in a conditional IF does not work:

      IF(MonthName(addmonths(Dato,0))= MonthName(addmonths(Today(),0)),sum({<Dato={">=$(=date(today()-1,'DD.MM.YYYY'))"}>}MyVariableToSum), sum({<MonthEndDt={'-1'}>}MyVariableToSum))

       

      Only the last condition is returned.

      I have checked MonthName(addmonths(Dato,0))= MonthName(addmonths(Today(),0)) returns -1 for all dates in current month and 0 for each date outside current month.

       

      Logic IF(TRUE, return This, else Return This) is my idea but its not working. What is it that I'm missing here?

       

      I'm a newbie in Qlik and your help is much obliged