1 Reply Latest reply: Jul 8, 2018 6:34 AM by Robert Hutchings RSS

    Difference between Current Week vs past week

    Darryl Fonseca

      Hi All,


      I need a bar chart which will show me the COUNT difference between past and current week


      I have an expression that gives me the count of the last week .( for example i have data from Week 1 till 25).

      This expression helps me to give the data for the max week in the data ( i,e. Week 25)

      Count({$<[Data Creation Date]={"$(= max({1}[Data Creation Date]))"}>}[Event ID])

      But i need help in getting the count for the past week ( i,e Week 24)

        • Re: Difference between Current Week vs past week
          Robert Hutchings



          Have you tired WeeksAgo (see comment by Alex)


          Relative Calendar Fields


          As I'm very new to Qlikview, this is a great help. One question though, how would I create a WeeksAgo calendar field? My best guess was


          52*(Week(Today())-Week(TempDate)) + Week(Today()) - Week(TempDate) as WeeksAgo


          but this doesn't seem to work

             Henric Cronström Legend  

            Henric Cronström Jan 13, 2014 10:00 AM (in response to Alex Hamilton McLeod)

          The Week() function "restarts" at 1 every year, so you cannot use this if the two dates span two years. No, I would probably do

               (WeekStart(Today()) - WeekStart(Date))/7

          instead. To understand what this does you need to understand how QlikView handles dates. Read about this here. But to make a long story short: The WeekStart() function returns an integer corresponding to the day. So by taking the diff and dividing by 7, you get the number of weeks ago.