Calculate yearly and quarterly values based on most recent monthly totals

[richard_pearce6]

Hi,

My data is monthly and quarterly and yearly stats would be the latest monthly total (not summed up months). For example, if my data were:

Load * Inline [
Month,Qtr,Year,Amount
1,1,2012,10
2,1,2012,10
3,1,2012,10
4,2,2012,20
5,2,2012,20
6,2,2012,20];

Monthly Results would be as above 10,10,10,20,20,20

Quarter 1 Results would be 10

Quarter 2 Results would be 20

Yearly Results would be 20

I could script this into a data table but I thought there would be an elegant solution. My final design would have a chart with a cycle group allowing the user to change between; Month, Qtr and Year. I've tried a few idea's using different functions but nothing seems to get the results I'm after.

Can anyone assist please?

[swuehl]

You can probably use

=FirstSortedValue(Amount, -Month)

to return the amount for the latest month, depending on your group by context, you should get what you want.

Hope this helps,

Stefan

[richard_pearce6]

Morning Stefan, thanks for your response.

I did try using FirstSortedValue and even tried using it with an aggr function with no luck. Unfortunately your example below (directly copied in) gives a null value, could you give me a bit more context as perhaps there's something further I need to do to get it working?

Many thanks

Richard

[richard_pearce6]

Hi again,

Sorry my last post wasn't right. Your expression did work on the dataset and gave the correct results. I over simplyfied the question for the purpose of here. The actual monthly data is sumed up from multiple lines; for example:

Load

* Inline [
Qtr,Month,Year,Amount
1,1,2012,1
1,1,2012,2
1,2,2012,10
1,2,2012,20
1,3,2012,100
1,3,2012,200
2,4,2012,300
2,4,2012,400
2,5,2012,500
2,5,2012,600
2,6,2012,700
2,6,2012,800]

;

[swuehl · Accepted solution]

Richard,

try

=FirstSortedValue(aggr(sum(Amount),Month),-aggr(Month,Month))

[richard_pearce6]

Perfect!! Worked a treat!! Thanks Stefan