3 Replies Latest reply: Nov 20, 2009 7:13 PM by MManders RSS

    Calculation with Month's Last Day

      Hi, first post here, so forgive the newbie question:)

      I'm using QVPE to evaluate it's possible uses for my company, I'm trying to reproduce the functionalities of a quite complex xls with qlikview in order to produce something more usable and appealing for one of our customers.

      Unfourtunately, I'm stuck with a "stupid problem" atm

      I have a serie of rows, each with 3 different dates (Recived, Closed, Limit)

      I want to show some statistics based on those dates and grouped by the different months

      I've loaded a calendar from an excel table with the script:

       

      LOAD DATA,

      month(DATA) as MONTH,

      MonthEnd(Date([DATA])) as ENDMONTH

      FROM

      Calendar.xls

       

      In qlikview, I've a pivot table with MONTH as dimension, and then the various expression, like:

      MONTH-- stat-1--stat2--

      jan

      feb

      mar

      One expression, which I know works fine is:

      TEXTCOUNT (DISTINCT IF (MONTH(Closed) > MONTH AND MONTH(Received) <= MONTH,ID)) + TEXTCOUNT (DISTINCT IF (ISNULL(Closed) <> 0 AND MONTH(Received) <= MONTH,ID))

      which returns the number of "non closed" for each of the months shown

      Now, I want to show the average "age" (in days) for the "non closed" for each months

      I've tried many ways but none worked and I'm a little lost now as I can't see the mistake:

      avg( distinct IF ((MONTH(Closed)>MONTH or ISNULL(Closed) <>0 ) AND MONTH(Received)<=MONTH, DAY(ENDMONTH)-DAY(Received)))

      I know that what isn't working is DAY(ENDMONTH)-DAY(Received), but I really can't see why...

      I someone can point me in the right direction it would be really appreciated :)

      many thanks in advance

        • Calculation with Month's Last Day
          MManders

          Day(Month) gives you the day number in the month, so 22, or 31, or ...

          day( '1971-10-30' ) returns 30.

          When you subtract the days, you don't automatically receive the right number of days, since there could be a month transition or a period of several months.

          What you need it to subtract the day numbers from eachother is the actual number that represents the date. Use Num(Date) to get that number.

          Num(Day(ENDMONTH)) - Num(Day(Received))

            • Calculation with Month's Last Day

              I tried but it didn't work

              I think there should also be something wrong about the logic because I'm receiving really weird numbers as output

              BTW, in the end I managed to have the correct result but really I can't tell why the formula is working.

              I used this to take account of substraction when the dates are from different months:

              MONTHEND(Received,MONTH-MONTH(Received)) - Received

              What is strange is that:

              1- I used a no distinct AVG formula

              2-I noticed that the output of the nodistinct formula was adding 1 to the correct result, so I used :

              (AVG( nodistinct expression) -1).

              Honestly, I haven't understood why it is working, there's something I'm missing about logic here

              Guess I'll have to make more tests to see if it works even with a different dataset