I have a finance cube where I use the Lastnonempty function to get the last balance of an account. This works fine in the time dimension, but when I look at the account hierarchy, e.g. a parent account it does not display the sum of the children but the lastnonempty value. Even when I specify an unary operator, the result is the same.

PS. This runs currently on a Developer Edition.

/SoerenK

Are you explicitly using LastNonEmpty as your aggregation function or have you enabled Account Intelligence and have assigned LastNonEmpty to this specific account type? If you have not gone through the process of setting up Account Intelligence, unary operators will not work.

B.

Hi B

I have tried both solutions, but with the same result.

As far as I can interpreted the books a semiadditive aggregation like the lastnonempty is semi in the sense that it gives the last nonempty value along the time dimension, but acts a an ordinary sum in all other dimensions?

/SørenK

".. a semiadditive aggregation like the lastnonempty is semi in the sense that it gives the last nonempty value along the time dimension, but acts a an ordinary sum in all other dimensions .." - but maybe in your scenario, the child values, which you're expecting to be sum, occur at different points on the time dimension? In that case, only the values at the last point in the time dimension with data willget summed.

Hi

I don't think that is the case here as Ihave the correct values at all the leaves. I have included an example here fromExcel where the problem is visible. The Amount is the movement amount in the finance cube and the balance is calculated in the ETL process on all balance accounts. AsI only have 9 month of financial data within the first fical year including opening balances, I can use the movement Amount as a reference for the balance calculations.

As you can see in the spreadsheet summing up to a parent for the balance value looks as it finds a lastnonempty value and inserts that at the parent.

Sorry that part of the example is in Danish, But you only need to know that AKTIVER = ASSETS

	Values
Row Labels	Balance	Amount
AKTIVER	15.487.833,98	365.324.865,08
ANLÆGSAKTIVER	505.662,87	-1.926.768,65
I. Immaterielle anlægsaktiver	2.706.433,17	9.613.283,72
Goodwill	2.706.433,17	9.613.283,72
GOODWA	-709.650,00	-709.650,00
GOODWAPR	-4.992.500,00	-4.992.500,00
GOODWPR	9.500.000,00	9.500.000,00
INDRLEJA	-361.200,00	-361.200,00
INDRLEJAPR	-986.946,71	-986.946,71
INDRLEJPR	4.457.147,26	4.457.147,26
INDRLEJTG	2.706.433,17	2.706.433,17
II. Materielle anlægsaktiver	-1.708.335,87	48.495.692,46
III. Finansielle anlægsaktiver	505.662,87	-60.035.744,83
OMSÆTNINGSAKTIVER	15.487.833,98	367.251.633,73
PASSIVER	-70.246.669,66	-355.010.697,79
Resultat	-20.715.345,85	-10.314.167,29
Grand Total	-75.474.181,53	,00

As you can see in the example the total of Goodwill should have been 9.813.283,72, but it is 2.706.433,17. Exactly the same as the account INDRLEJTG. You can also see that it gets the correct values at the leaves, but the sum is incorrect.

PS. one thought I have had is that my time dimensionhave a date field as a primary key and not an integer TimeKey as I have seen in SSAS Step By Step bookfrom Microsoft. Could that have an impact?

/SoerenK

The last value (balance = 2.706.433,17) occurs in the transaction below:

AcctName BookipingDate Amount Balance

Indr lej lokaler tilgang

2007-09-01 00:00:00

22660

2706433,17

None of the orther transaction/balances in that part of hierarchy is dated in September, The closest i get is 2007-08-31.

Does that influence on how the lastnonempty funtion sums across non time dimensions?

Hi,

I was struggling with this concept too. When working with individual cell, the lastnonempty() is very straightforward. When working with multiple cells and aggregation, this is where the confusion arises. What lastnonempty() does on the aggregation of multiple members on non time dimension is: find the lastest time member where one of the non time members has value on, then apply sum across the members on this latest time member. This is exactly what happened in your case, only one line has value on sept, which is the lastest time being applied to the rest of lines resulting no values except this line.

This is really a calculating order issue. Ideally, you wish it would calculate the individual member using lastnonempty() function (each member get values on different last time member possibly), then sum the results, which seems more useful to me.

Hi All

Tkanks for the help it has been very useful.

Just to summarise:

The Lastnonempty function only add values, in non time dimensions,that are aligned in the time dimension. Meaning all balance transactions must have the same date as onlybalances of the same newest date will be added.

I tried to alignall the latesttransactions to the same date - and it now works!

This leaves my with a newproblem:

The financecubewill contain around 12dimensions (site,department, group , enterprise, .....). Lets say on an average there are 10 entries in each dimension and about 1500 account number. Then we need to generate 1500 * (12)^10 rows to cover all dimensionsat each time grain. There must be a better way?

Again thanks

/SoerenK

Hi,

Have you considerred Deepak's suggestion to use rollup operator? I am also thinking that you might be able to define calculation to achieve what you want. If your data member is in sort of hieararchy structure, you could calculate the goodwill item by summing its children member against lastnonempty measure.

Sum([Goodwill].children, [measures].[balance]).

I have not tried this myself,but it is something worth a try. I don't know how the current time member play a role in this situation.