3 Replies Latest reply: Jul 30, 2016 2:42 PM by Steve Br. RSS

    Comparing periods

    Steve Br.

      Hello!

       

      I need some help.

       

      I would like to create KPI objects that compare costs, volume, and distance and are filter sensitive. So basically, if January, February, March and April are selected, I want the KPIs to compare April vs. March. If April is excluded from the selection and only CountryA is selected, then the KPIs should compare March vs February for CountryA, etc. So something like this would be great:

       

      Untitled.png

       

      I did read a lot of similar topics here, but I never managed to come close to what I want.

       

      This is a sample of the data I am working with.

       

      CountryDateDriverCostsVolumeDistance
      CountryA05.01.2016Driver133517220298
      CountryA05.01.2016Driver126418413265
      CountryA05.02.2016Driver126715775240
      CountryA05.02.2016Driver238416897239
      CountryA05.02.2016Driver222921664175
      CountryA05.03.2016Driver233516550148
      CountryA05.03.2016Driver333317114143
      CountryA05.04.2016Driver321618841287
      CountryA05.04.2016Driver329316971196
      CountryA05.04.2016Driver329420533339
      CountryB05.01.2016Driver430118949149
      CountryB05.01.2016Driver420716415272
      CountryB05.02.2016Driver425019991162
      CountryB05.02.2016Driver528820089226
      CountryB05.02.2016Driver528519417113
      CountryB05.03.2016Driver522220062133
      CountryB05.03.2016Driver528915887202
      CountryB05.04.2016Driver623820430211
      CountryB05.04.2016Driver635920099155
      CountryB05.04.2016Driver623421839297
      CountryC05.01.2016Driver733217022288
      CountryC05.01.2016Driver724016451217
      CountryC05.02.2016Driver731318548134
      CountryC05.02.2016Driver839016733292
      CountryC05.02.2016Driver831119139251
      CountryC05.03.2016Driver833521726230
      CountryC05.03.2016Driver923718130340
      CountryC05.04.2016Driver927621143348
      CountryC05.04.2016Driver936819538184
      CountryC05.04.2016Driver938817782122

       

       

      I would appreciate any help!

        • Re: Comparing periods
          Sunny Talwar

          Like this:

           

          Capture.PNG

           

          Expressions Used

          Sum({<Date = {"$(='>=' & Date(MonthStart(Max(Date))) & '<=' & Date(MonthEnd(Max(Date))))"}>}Costs)

          Sum({<Date = {"$(='>=' & Date(MonthStart(Max(Date), -1)) & '<=' & Date(MonthEnd(Max(Date), -1)))"}>}Costs)


          Sum({<Date = {"$(='>=' & Date(MonthStart(Max(Date))) & '<=' & Date(MonthEnd(Max(Date))))"}>}Volume)

          Sum({<Date = {"$(='>=' & Date(MonthStart(Max(Date), -1)) & '<=' & Date(MonthEnd(Max(Date), -1)))"}>}Volume)


          Sum({<Date = {"$(='>=' & Date(MonthStart(Max(Date))) & '<=' & Date(MonthEnd(Max(Date))))"}>}Distance)

          Sum({<Date = {"$(='>=' & Date(MonthStart(Max(Date), -1)) & '<=' & Date(MonthEnd(Max(Date), -1)))"}>}Distance)

          • Re: Comparing periods
            Vineeth Pujari

            Period Presets: Compare Periods on the fly

             

             

            if you do this in your script using presets, your expressions will then be simplified to something as below.

             

            CurrentMonth = Sum({<Period= {$("=MAX(MONTH)"}>}Costs)

             

            Previous Month = Sum({<Period= {$("=MAX(MONTH)-1"}>}Costs)


             



            • Re: Comparing periods
              Steve Br.

              Thank you guys for the help! I did it as Sunny suggested, it works exactly as I wanted. Awesome!

               

              Thank you again!