3 Replies Latest reply: Apr 2, 2017 8:18 PM by Sunny Talwar RSS

    Accumulate days in a table

    juan prado

      Hey guys! I need your help to translate a calculated field in excel to qlikview script.

       

      I have this table in excel

       

      IDreasondate_fromdate_to
      1A101/01/201730/01/2017
      1A101/01/201714/01/2017
      1B115/01/201715/02/2017
      1C113/02/201728/02/2017
      2A105/02/201707/02/2017
      2B106/02/201715/02/2017


      I need to generate new fields that I could perform in Excel but in the script


       

      IDreasondate_fromdate_toQ_DAYSQ_DAYS_REASONTOTAL_Q_DAYS
      1A101/01/201730/01/20173030

      59

      1A101/01/201714/01/201714059
      1B115/01/201715/02/2017311659
      1C113/02/201728/02/201716

      13

      59
      2A105/02/201707/02/20173311
      2B106/02/201715/02/201716811


      For the field Q_DAYS I made the following formula in Excel: DATE_TO - DATE_FROM + 1


      For the field TOTAL_Q_DAYS I made =+MAX(date_to)-MIN(DATE_from)+1


      For the Field Q_DAYS_REASON i made the following: the Q_DAYS for the min value of (date_from) is the same and in the second field I made =+IF(QDAYS>previous(Q_DAYS),Date_to-previous(Date_to),0)


      What i need to do in this field is calculate If this date_To is greater than the previous date_to, add up the difference between the dates_to


      for example:

          

      IDreasondate_fromdate_toQ_DAYSQ_DAYS_REASON
      1A101/01/201730/01/2017            30.00                   30
      1A101/01/201713/02/2017            14.00                   44
      1F115/01/201715/02/2017              2.00                  31
      1G313/02/201728/02/2017            13.00                  16

       

      thanks!!!

       

      stalwar1