Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Richard,
try
=FirstSortedValue(aggr(sum(Amount),Month),-aggr(Month,Month))
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
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
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]
;
Richard,
try
=FirstSortedValue(aggr(sum(Amount),Month),-aggr(Month,Month))
Perfect!! Worked a treat!! Thanks Stefan