I am facing a problem. I have the following table (see below), with Person ID, Event ID, Date, Event Category (A-L), Value per event and:
I want to calculate the value spent per person (since ever) -> that's fine, using an aggr function with person ID (spent = 1 means the person spent some money in that event).
Then I want to calculate the value spent per person per year (having a certain year selected, I want to know how much each person spent for example in 2013). -> I have to aggregate person ID and year.
I'm using this formula:
=max(aggr(max(aggr(
sum({<[Spent]={1} >}[Value])
, [Person ID])), Year))
but it only returns the person who spent most each year, as shown in the picture.
And afterwards I would like to calculate the value spent per person per month (having a certain year and month selected, I want to know how much each person spent for example in January 2015). -> I have to aggregate person ID, year and Month.
Within a table which contained the detail-dimensions you didn't need an aggr-function - they are only useful if you have a consolidated view and wanted to know the aggregation of a calculation on lower dimensions and then you would need sum(aggr(sum for this and not max(aggr(sum.
In your table you need more something like this: sum(TOTAL <Year> Value).
Hi, thanks for your reply. Unfortunately, what you are saying does not work (because I need set analysis to say spent = 1).
If I take out the aggr function, I get the sum for each row, which means that value per event = value spent per person (since ever). Each row returns a different value.
I didn't meant to remove the set analysis conditions - this was only for reasons to simplify the logic that you rather need an expression-extension to TOTAL instead to use an aggr-wrapping.
