3 Replies Latest reply: Jan 10, 2014 10:44 AM by Piet Hein van der Stigchel RSS

    Date, Periods, Weeks & Quarters in QlikView

      Hi

       

      I am trying to figure out a problem which I cannot seem to solve.

       

      MY PROBLEM

       

      I have an Excel file with Years, Weeks and Quarters and they have the following structure

       

       

      PERIODQUARTERWEEKYEAR
      2014012014012014012014
      2013132013042013522013
      2013132013042013512013
      2013132013042013502013
      2013132013042013492013
      2013122013042013482013
      2013122013042013472013
      2013122013042013462013
      2013122013042013452013
      2013112013042013442013
      2013112013042013432013
      2013112013042013422013
      2013112013042013412013
      2013102013032013402013
      2013102013032013392013
      2013102013032013382013
      2013102013032013372013
      2013092013032013362013
      2013092013032013352013
      2013092013032013342013
      2013092013032013332013
      2013082013032013322013
      2013082013032013312013
      2013082013032013302013
      2013082013032013292013
      2013072013022013282013
      2013072013022013272013
      2013072013022013262013
      2013072013022013252013
      2013062013022013242013
      2013062013022013232013
      2013062013022013222013
      2013062013022013212013
      2013052013022013202013
      2013052013022013192013
      2013052013022013182013
      2013052013022013172013
      2013042013012013162013
      2013042013012013152013
      2013042013012013142013
      2013042013012013132013
      2013032013012013122013
      2013032013012013112013
      2013032013012013102013
      2013032013012013092013
      2013022013012013082013
      2013022013012013072013
      2013022013012013062013
      2013022013012013052013
      2013012013012013042013
      2013012013012013032013
      2013012013012013022013
      2013012013012013012013
      2012132012042012522012
      2012132012042012512012
      2012132012042012502012
      2012132012042012492012
      2012122012042012482012
      2012122012042012472012
      2012122012042012462012
      2012122012042012452012
      2012112012042012442012
      2012112012042012432012
      2012112012042012422012
      2012112012042012412012
      2012102012032012402012
      2012102012032012392012
      2012102012032012382012
      2012102012032012372012
      2012092012032012362012
      2012092012032012352012
      2012092012032012342012
      2012092012032012332012
      2012082012032012322012
      2012082012032012312012
      2012082012032012302012
      2012082012032012292012
      2012072012022012282012
      2012072012022012272012
      2012072012022012262012
      2012072012022012252012
      2012062012022012242012
      2012062012022012232012
      2012062012022012222012
      2012062012022012212012
      2012052012022012202012
      2012052012022012192012
      2012052012022012182012
      2012052012022012172012
      2012042012012012162012
      2012042012012012152012
      2012042012012012142012
      2012042012012012132012
      2012032012012012122012
      2012032012012012112012
      2012032012012012102012
      2012032012012012092012
      2012022012012012082012
      2012022012012012072012
      2012022012012012062012
      2012022012012012052012
      2012012012012012042012
      2012012012012012032012
      2012012012012012022012
      2012012012012012012012

       

      In my QlikView application, I have a list box which displays all these values in the table. Let's have a focus on the WEEK column in the above structure and the format it is in. (YYYYWW). The requirement for my application is to always have the front dashboard to display one less the maximum week number in the data. So if the maximum WEEK is 201352 then one less than this week is 201351. I have set an OnOpen trigger for the document to Toggle Select the WEEK filter to the following:


      MAX(WEEK) - 1

       

      This will acquire the maximum week number and get the one before it.

       

      If the current week is 201401 (the first week of 2014) then when I apply the above function it gives me 201400, which is not the value I am after as I am after 201352.

       

      Is there a way that I can get around this so that when I have a week I am deducting one week? Do I need to reconsider my function I am using?

       

      If anyone can help then that would be great.