3 Replies Latest reply: Jul 27, 2018 9:34 AM by Jerry Ile RSS

    avg between dates

    Jerry Ile

      Hi,

       

      I've read lots of posts but still can't crack this

       

      I'm using Qlik Sense and would like a 4 week average on sales units

      I have a variable for Current Week

      =Max([Start Wk])  is set as  vCurrentStWk

       

      My date field is [Start Wk]

      I noticed it has a timestamp e.g. 25/06/2018 00:00:00 so I have tried the following but still had no joy

       

      Avg({<date([Start Wk],'DD/MM/YYYY')={'>=$(vCurrentStWk)-28'}}>} [Sold Qty])

       

      I would love some help

       

      Thanks

        • Re: avg between dates
          David Štorek

          Hi,

          I don't think it is necessary to change date format by Start Wk. It seems as a little problem with syntax (definitelly you have extra } in expression)

          try it like following

           

          Avg({<[Start Wk]={">=$(=$(vCurrentStWk)-28)"}>} [Sold Qty])

            • Re: avg between dates
              Jerry Ile

              Thanks David but Still not working ;0(

               

              I have tried loading date without the timestamp into a new field called StartWKNTS and setting that as the Variable to avoid the formatting issue

               

              I noticed

              =$(vCurrentStartWK) shows as 15/07/2018

              but  =$(vCurrentStartWK)-28 shows as 43268

               

              num(Date#(StartWkNTS,'DD/MM/YYYY')) shows as 43289

               

              I tried variations on the following but still no Joy

              Avg({<[StartWKNTS]={">=$(=$(vCurrentStWk)-28)"}>} [Sold Qty])


              Avg({<num(Date#(StartWkNTS,'DD/MM/YYYY'))={">=$(=$(vCurrentStWk)-28)"}>} [Sold Qty])


              Avg({<num(Date#(StartWkNTS,'DD/MM/YYYY'))={">=$(vCurrentStartWK)-28"}>} [Sold Qty])

            • Re: avg between dates
              Shahbaz Khan Mohammed

              Try by removing time stamp from the date and then use it in the expression?

              Since your expression just has date format not the timestamp format.. thats why it work?