0 Replies Latest reply: Sep 9, 2018 9:54 AM by Cornel Rusu RSS

    How to calculate sum between two dates in Qlik Sense Cloud

    Cornel Rusu

      Hi,

       

      Im new to Qlik Sense Cloud and I try to make a calculation in a measure field between a start date (that is different for each product) and an end date using this:


      The form of expression that is not working:

      Sum( {$<DATATRAN.autoCalendar.Date = {">=$(=Min(Date(  FirstSortedValue({$<[RECEPTII.NRSHOP]={5}>}[RECEPTII.DATANIR], -[RECEPTII.DATANIR])) ))<=$(=Max(today(0)))"},[NRSHOP]={5} >} [VANZARI_OPERATIONALE.CANTITATE])

       

      start date should be this calculated using this expression that calculate what is the start that that should be used for each product (the start date will be different for each product):

      FirstSortedValue({$<[RECEPTII.NRSHOP]={5}>}[RECEPTII.DATANIR], -[RECEPTII.DATANIR]))

       

      the end date shoud always be:

      today(0)


      The expression in the above form does not generate the correct result but If I change the start date and I manually calculate the start date (using: " today(0) - 40 ") then the below form of the same expression is working just fine:

       

      The form of the expression that is working:

      Sum( {$<DATATRAN.autoCalendar.Date = {">=$(=Min(Date( today(0) - 40  )))<=$(=Max(today(0)))"},[NRSHOP]={5} >} [VANZARI_OPERATIONALE.CANTITATE])


      The problem is that I dont want to use the same start date (today(0) - 40) for all the calcualtions because each line has a different start date (FirstSortedValue({$<[RECEPTII.NRSHOP]={5}>}[RECEPTII.DATANIR], -[RECEPTII.DATANIR])))