4 Replies Latest reply on May 18, 2017 9:24 AM by Mark Lewis

    Period Comparisons using Aggregate Calculations

    Mark Lewis

      Hi all - I know there have been many, many posts asking similar questions - I have been through many of them, read the helpful responses, downloaded example workbooks...and yet still I'm stuck!


      I have this working in other situations using non-aggregate calculations, basically by creating separate calculated fields with built in date filters (using IF logic) and then calculating var & %vars on these calculations. No doubt there are more complex, elegant solutions, but simple works for me here.


      But when the comparison's I need are based on aggregate calculations, my world falls apart...


      To take an example, I have a  'Utilisation' KPI whose calculation is:


      I can use this in a table with a date filter in the Filters window, and all is fine. Obviously I can't compare to to Last Year in the same table, as that needs a separate date filter. I've tried to get % Difference Table calculation, but can't make head or tale of getting this to work.


      So I thought I'd try it in a formula.

      The thinking above is: If the year is the current year, and the date is less than the [MaxDate] parameter (because I need to limit the date range to the end of last month, rather than the current middle of the current month date it actually is, and so I can easily look at different end-date ranges), then show UtilisationNet. This should give me the Current YTD Utilisation.


      I figured I would then create a version of the above -364 days, and could then do my YTD comparisons.


      But it just results in zero. I had to use all those attr() functions because [UtilisationNet] is an aggregate calculation. (BTW, I've tried it with attr([MaxDate]) as well. and attr(year(now)) - makes no difference.


      This is incredibly frustrating. I'm sure I'm being really thick, but I can't get it to work. And all the responses to other, similar, questions, I can't seem to get my head around working for me either.


      So any help much appreciated!


      Thank you,


        • 1. Re: Period Comparisons using Aggregate Calculations
          Zhouyi Zhang

          Hi, Mark


          Is it possible to provide a sample workbook?



          • 2. Re: Period Comparisons using Aggregate Calculations
            Mark Lewis

            HI - thanks for the reply.


            Not easily...


            The workbook this comes from is 25MB in size, and full of confidential information.


            I was hoping I had surfaced enough info above to enable a response.


            Thank you!


            • 3. Re: Period Comparisons using Aggregate Calculations
              Okechukwu Ossai

              Hi Mark,


              If it results in zero it means that the result of your formula is False. It is tough diagnosing the internal working of a workbook without seeing an anonymized version. You don't need to share anything sensitive, a small size dummy data will do. Otherwise, every response here will be speculation.

              My general advice will be to look at your formula again. If a formula is not returning the expected result, the best way to troubleshoot it will be breaking it down to its constituent parts.


              So I will create a few individual formulas and place them side by side in a table to see what the problem is.

              Create these separate calculate fields;


              1. ATTR(YEAR([Date]))

              2. Year(NOW())

              3. ATTR([Date])

              4. [MaxDate]

              5. [UtilisattionNet]

              6. ATTR(YEAR([Date])) = Year(NOW())

              7. ATTR([Date]) <= [MaxDate]

              8. ATTR(YEAR([Date])) = Year(NOW()) AND ATTR([Date]) <= [MaxDate]


              Put these 8 fields in a table side by side and compare their results. You will immediately see what is causing the expression to return False and consequently zero. Since you've exceeded the default 6 display columns, remember to go to Analysis - Table Layout - Advanced. Change Max row and column labels to 16.


              Hope this helps.


              • 4. Re: Period Comparisons using Aggregate Calculations
                Mark Lewis

                That's a really helpful suggestion, Ossai. Thank you.


                I will try this tomorrow and let you know what comes out.