2 Replies Latest reply: Feb 1, 2018 6:32 AM by ruti t RSS

    Issue with Measure in QlikSense

    ruti t

      Hi,

       

      I create measure that present sum of sales

      when the user selection in field [Snapshot Date] Minus One Month equal to field [Prev Snapshot Date]:


      Sum(IF(FLOOR(date([Prev Snapshot Date],'MM/DD/YYYY')-date(MonthEnd(AddMonths([Snapshot Date],1)),'MM/DD/YYYY'))=0,Sales))


      It doesn't work..


      Could you please advise?


      Thank you!

        • Re: Issue with Measure in QlikSense
          Ali Hijazi

          floor takes numeric values whereas you are passing date values
          you can write the following:
          sum(if(num(daystart([prev snapshot date])) - num(daystart(monthEnd(date(addmonths(snapshotdate,1))))) = 0 ,sales)

           

          keep in mind monthEnd expects a date value where as you are passing a numeric value returned by addmonths function

            • Re: Issue with Measure in QlikSense
              ruti t

              Thank you for your response..

               

              It doesnt work as you suggest ..

               

              Maybe you mean to  -  addmonths(snapshotdate,-1) :

               

              sum(if(num(daystart([prev snapshot date])) - num(daystart(monthEnd(date(addmonths(snapshotdate,-1))))) = 0 ,sales)


              if I do so it brinfs me the sum of sales in cueenrt date selection and not cueenrt date selection minus one month