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

# Period Comparisons using Aggregate Calculations

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,

Mark

• ###### 1. Re: Period Comparisons using Aggregate Calculations

Hi, Mark

Is it possible to provide a sample workbook?

ZZ

• ###### 2. Re: Period Comparisons using Aggregate Calculations

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!

Mark

• ###### 3. Re: Period Comparisons using Aggregate Calculations

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.

Ossai

• ###### 4. Re: Period Comparisons using Aggregate Calculations

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

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

Thanks.

Mark