9 Replies Latest reply: Aug 28, 2018 11:08 PM by Petter Skjolden RSS

    Challenge: Adjust table according to week filter

    Martijn Gorter

      I got a real challenge for you guys. I really hope somebody is going to help me, please be my guest and try to build it yourself.

       

      I have added the source and the wanted outcome, which should speak for itself but some extra text below.

       

      I want to have a filter with the week. If you select a week, you should see that week in the outcome including -1 week and + 3 weeks. (like the example). If you selected a week below current week it should take next year. It is not needed to select father then one year from now. There is also a challenge when you select for example week 51, then i would like to see week 50, 51, 52, 1 and 2.

       

      I think it shoud be like 5 expressions, with a formula but have not figured out how, but any other idea is welcome.

       

      I really hope anybody is able to show me how this could be done in QlikSense, you can sent me the app or an example of the formulas.

       

      If you have any questions, please do not hesitate to ask.

       

      Kind Regards Martijn

        • Re: Challenge: Adjust table according to week filter
          Petter Skjolden

          Here is something that is quite close to what you are looking for I believe. You just have to tweak the expressions and possibly the weekstart day to finetune it. I leave that to you:

           

          2018-08-22 19_50_54-Qlik Sense Desktop.png

            • Re: Challenge: Adjust table according to week filter
              Martijn Gorter

              Thank you so very much Petter. I was lookging all day yesterday if somebody answered but your answer, just popped up this morning.

               

              I have 2 additional questions:

               

              1) Can i do this without the crosstable in the script, because my original script is very long with a very long linktable.

              So would be nice if i do not have to change that.

               

              2) Can the results also be presented in a straight table, because i wanna use lots of conitional formating, that is not available in the pivot table at the moment.

               

               

              I really appreciate your help, you have no idea how much;-)

                • Re: Challenge: Adjust table according to week filter
                  Petter Skjolden
                  1. It is even easier load script wise... just keep the source table format(s). Note: A CrossTable operation is very efficient and the table format you get is more tuned for analysis and BI puposes. I think keeping the format is less efficient but I do understand that with link-tables it could be a bit more complicated to handle.
                  2. Yes that is also relatively easy.

                      a) You will have to have a synthetic dimension to give the rows for the Expected, Real and Stock:

                                  =ValueList('Expected','Real','Stock')

                      b) You will have to create five measures with a Pick() and Match() combination to get the right sum

                            according to which row that is displayed. For the first measure it will look like this:

                   

                  Pick( Match( ValueList('Expected','Real','Stock'), 'Expected','Real','Stock')

                    ,Sum( {1<Date={">=$(=WeekStart(Min(Date),-1,0))<=$(=WeekEnd(Min(Date),-1,0))"}>} Expected)

                    ,Sum( {1<Date={">=$(=WeekStart(Min(Date),-1,0))<=$(=WeekEnd(Min(Date),-1,0))"}>} Real)

                    ,Sum( {1<Date={">=$(=WeekStart(Min(Date),-1,0))<=$(=WeekEnd(Min(Date),-1,0))"}>} Stock)

                  )

                  For the next four measures you only have to change the bolded -1 in the above expression to the 0,1,2 and 3.

                   

                  I have attached an updated version of the app.