I am having some problems with set analysis and the aggr function.
In my application, the data on which the KPI's are based is delivered monthly, with some data lagging behind a few periods. For example, at the end of March, I receive data from the CRM system for March, but the Financial data that I get is from January. I am trying to create a straight table that, for each KPI, shows the name of the KPI, the last period for which data is available and the value for that KPI and Period. For example:
KPI Period Value
------------------------------------------------------------------------
Revenue january 2010 100
Customer visits march 2010 500
------------------------------------------------------------------------
I also need to show trends, so I also load historic data. The simplified version of the fact table contains these fields: MonthKey, ItemKey and Value.
I came up with the following expression to show the value for the last available month for the item:
sum( {$<MonthKey={$(=Aggr(Max(MonthKey), ItemKey))}>} Value) This doesn't return the correct result. The problem seems to be in the "Aggr(Max(MonthKey), ItemKey)" part, the expression ignores the ItemKey dimension and just returns the max MonthKey for the entire dataset, which is march 2010. The result is that values for lagging items are not displayed, resulting in the following straight table:
KPI Period Value
------------------------------------------------------------------------
Revenue january 2010
Customer visits march 2010 500
------------------------------------------------------------------------
The "Aggr(Max(MonthKey), ItemKey)" expression works fine when I use it on it's own, it only returns the wrong result when I use it in set analysis. How can I rewrite the expression so that the max MonthKey returned is the max MonthKey for the ItemKey, and not for the entire dataset?
