2 Replies Latest reply: Apr 25, 2018 5:59 AM by Sophie Guyennon RSS

    Comparative CA N / CA N-1, current month problem

    Sophie Guyennon

      Hello,

      I started on Qlik Sense and I had Designer Training and Developper not completely yet. I have days left.

      My question is more about a syntax problem.

      I created a table where I want to get the variation of the turnover according to the current month.

      I tried several things to create my column.

      If I put this, it works:

       

      sum ({<[_ Type Movement] = {'Sales'}, [Article Type] = {'Article'}, [Type Register] = {'Invoices'}, [Keyword] - = {'SAV'}, [ Year] = {'$ (= max (Year))'}, [Month] = {"<5"}>} [Amount Line])

      /

      sum ({<[_ Type Movement] = {'Sales'}, [Article Type] = {'Article'}, [Type Register] = {'Invoices'}, [Keyword] - = {'SAV'}, [ Year] = {'$ (= max (Year) -1)'}, [Month] = {"<5"}>} [Amount Line])

      -1

       

      On the other hand, if I try to dynamically recover the current month it does not work.

      LET v_monthsCurrent = max (Month);

       

      sum ({<[_ Type Movement] = {'Sales'}, [Article Type] = {'Article'}, [Type Register] = {'Invoices'}, [Keyword] - = {'SAV'}, [ Year] = {'$ (= max (Year))'}, [Month] = {"<$ (v_OnCurrent)"}>} [Amount Line])

      /

      sum ({<[_ Type Movement] = {'Sales'}, [Article Type] = {'Article'}, [Type Register] = {'Invoices'}, [Keyword] - = {'SAV'}, [ Year] = {'$ (= max (Year) -1)'}, [Month] = {"<$ (v_OnCurrent)"}>} [Amount Line])

      -1

       

      I tried several things:

      - Put directly into the code [Month] = {'<$ (= max (Month))'}

      - Put directly into the code [Month] = {'<$ (max (Month))'}

      I must not be too far away. A little idea, please? We did tests with the trainer and he did not arrive either (without variable)

      Thank you for your help

        • Re: Comparative CA N / CA N-1, current month problem
          Lisa Phillips

          Hi Sophie,

           

          I think the issue is with your logic.  The max month will be 12 from previous years.  The max year will be this year.

           

          If your data is current, there is a today() function that could be used to derive the current month.

               ... , Month={$(=month(Today())}>}[Amount Line])

           

          See how this goes for you.

            • Re: Comparative CA N / CA N-1, current month problem
              Sophie Guyennon

              Lisa, thank you for your help. I well get the month but I want <= datas.

               

              With this code I get nothing

               

              sum({<[_Type Movement]={'Sales'},[Type Article]={'Article'},[Type Register]={'Factures'}, [Mot cle]-={'SAV'}, [Year]={'$(=max(Year))'}, Month={"<$(<=month(Today()))"} >}[Amount Line])

              /

              sum({<[_Type Movement]={'Sales'},[Type Article]={'Article'},[Type Register]={'Factures'}, [Mot cle]-={'SAV'}, [Year]={'$(=max(Year)-1)'}, Month={"<$(=month(Today()))"} >}[Amount Line])

              -1

               

              Where should I place the < sign ?