4 Replies Latest reply: Apr 23, 2018 12:22 PM by Sunny Talwar RSS

    Qlik Sense - Set Analysis, calculate delta for two time periods

    Kai Nehen

      Hi All,

       

      first of all, I would like to thank everyone that has been / is contributing to this community. It seems like a very lively one and good at that too. I have been trying to answer my question for the past 2 hours, but to no avail.

       

      My data looks like this:

       

      Reporting DateTenantsTotal Contractual Income
      31/03/2018A500
      28/02/2018A400
      31/01/2018A500
      31/12/2017A400
      31/03/2018B485
      28/02/2018B444
      31/01/2018B421
      31/12/2017B158

       

      I am trying to set up a pivot table which will include the delta between two periods, ideally the most current (31/03/18 and 28/02/2018 or 31/12/2017, alternatively) across both tenants. I have tried the following:

       

      sum({<[Reporting Date.Date] = {"$(=max([Reporting Date.Date]))"}>} [Total Contractual Income]) - sum({<[Reporting Date.Date] = {"$(=max([Reporting Date.Date],2))"}>} [Total Contractual Income])
      

       

      The result of this is 0, whereas it should not be 0.

       

      Does anybody have an idea why the above formula is not working?

       

      Thanks,


      Kai

        • Re: Qlik Sense - Set Analysis, calculate delta for two time periods
          Sunny Talwar

          Try this

           

          Sum({<[Reporting Date.Date] = {"$(='>=' & Date(AddMonths(Max([Reporting Date.Date]), -1), 'DD/MM/YYYY') & '<=' & Date(Max([Reporting Date.Date]), 'DD/MM/YYYY'))"}>} [Total Contractual Income])

            • Re: Qlik Sense - Set Analysis, calculate delta for two time periods
              Kai Nehen

              Hi Sunny,

               

              thanks a lot for answering so quickly. This formula works in principle on my data. What it does though is adding the two together.

               

              Suppose my total for 31/03/18 is 985 and mu total for 28/02/2018 is 844. Your formula would yield 1,829 as a result. How do I need to modify it in case I wanted to calculate 985 - 844 = 141?

               

              I have tried adjusting it myself, but cannot make it work.

               

              Many thanks,


              Kai

                • Re: Qlik Sense - Set Analysis, calculate delta for two time periods
                  Sunny Talwar

                  May be this

                   

                  Sum({<[Reporting Date.Date] = {"$(='>=' & Date(AddMonths(Max([Reporting Date.Date]), -1), 'DD/MM/YYYY') & '<' & Date(MonthStart(Max([Reporting Date.Date])), 'DD/MM/YYYY'))"}>} [Total Contractual Income])


                  -


                  Sum({<[Reporting Date.Date] = {"$(='>=' & Date(MonthStart(Max([Reporting Date.Date])), 'DD/MM/YYYY') & '<=' & Date(Max([Reporting Date.Date]), 'DD/MM/YYYY'))"}>} [Total Contractual Income])

              • Re: Qlik Sense - Set Analysis, calculate delta for two time periods
                Felip Drechsler

                Hi Kai,

                 

                I'm assuming your date field is not being interpreted as a number, instead, it's a string and the set analysis will not work.

                 

                With your data, i did:

                 

                data:

                Load

                Date#([Reporting Date],'DD/MM/YYYY') as [Reporting Date],

                Num(Date#([Reporting Date],'DD/MM/YYYY')) as [Reporting Date Num],

                Tenants,

                [Total Contractual Income];

                Load * Inline

                [

                Reporting Date,Tenants,Total Contractual Income

                31/03/2018,A,500

                28/02/2018,A,400

                31/01/2018,A,500

                31/12/2017,A,400

                31/03/2018,B,485

                28/02/2018,B,444

                31/01/2018,B,421

                31/12/2017,B,158

                ]

                 

                And with your expression: sum({<[Reporting Date Num] = {"$(=max([Reporting Date]))"}>} [Total Contractual Income]) - sum({<[Reporting Date Num] = {"$(=max([Reporting Date],2))"}>} [Total Contractual Income]) 

                 

                I obtained:

                sample.png

                 

                Tip: it's much easier to deal with dates in a numeric format instead of the textual one and the example above can be done in Sense, just made it on view because it's the one i have installed.


                Felipe.