22 Replies Latest reply: Jul 17, 2012 10:34 AM by nima ebrahimzadeh

Total aggregation of dimension

Hello Guys,

I have a problem with total aggregation in dimension.Please check the attached and let help me to find the solution.

I want the total of Nb. Cust at.

Thanks

• Total aggregation of dimension

Maybe removing the year from the aggregation?

• Total aggregation of dimension

Not sure if I understand, you want which column to be aggregated as Total?

As far as I see, Nb. Cust is a dimension, you usually don't aggregate dimension values, but expression values.

• Total aggregation of dimension

I want to see the total of the   aggregation (dimension )  it means total of (Nb.cust) as a partial total.

Is there any way to achive it.

Thanks

• Re: Total aggregation of dimension

Nima, I dont think i understand.  Can you ilustrate the result you want to achive?

Why do you have Nb.cust as a dimension instead of expression?

• Total aggregation of dimension

Ok,

Here is the case:

If you look at the top of the pivot table you'll see the Year and then the measures.

If I add the Nb.Cust in the expr then it'll repeated for every single year which we don't want to.

I hope explain it clearly

Thanks,

• Re: Total aggregation of dimension

I think I understand.

What about faking the year dimension with set analysis?

• Total aggregation of dimension

Thanks Marcus,

I had  done it before for my client but he did not lik the fake one. He want to have a real year on the top and the measures as the nested column.

Nima,

• Total aggregation of dimension

Maybe you could use an additional dimension with values 'Sales', 'Tons', 'Margin'  (created in the script).

Then use only one expression, in which you check /match() for your measure dimension value and pick() the appropriate aggregation function.

Up to this this will just rebuild what you already have. Adding a total for the horizontal dimension Year, and using a check for secondarydimensionality() should allow you to use your NB Cust aggregation in that separate column (the reused Total column).

• Total aggregation of dimension

Could you pleae give me an example of the script?

Thanks

• Re: Total aggregation of dimension

Hi Nima

I made two ugly solutions that might give some ideas.

• Total aggregation of dimension

Thanks for your help but I'm still missing the partial total

• Re: Total aggregation of dimension

Example attached.

• Total aggregation of dimension

Thanks Swuehl,

That was a nice solution.

Thanks

• Re: Total aggregation of dimension

Hi Swuehl,

I have a question on this problem you solved long time ago.

if we want to add another( or more) column like NB_CUST , what should we do?

I really appreciate if you can help on this too

• Re: Total aggregation of dimension

Yes, that's all a long time ago.

As far as I see, it would be really complicated if even impossible to achieve what I think you want following my approach. There is only one total column for the context / position you need, so the only possibility I see is either concatenating your different values (maybe separated by a delimiter / line break) in the single one total expresssion, or using a different approach (which I haven't time to think about right now).

You might want to open a new thread for this new problem, so you also get more attention from others.

• Re: Total aggregation of dimension

Thanks ,

That was the same thing I was thinking.

Thanks again.

Nima

• Re: Total aggregation of dimension

I dont know if i understand you correct, but if you move the calculated dimension to the left, and just add a partial sum, will that solve your problem?

• Total aggregation of dimension

Thanks Marcus,

We want to see a number for each row and the total( or the partial total) at the end.

If you have any idea, I appreciate.

Thanks,

Nima

• Total aggregation of dimension

If you use something like sum(NB_CUST) as expression, you get your totals, but I assume that is not what you want, you want only one expression evaluation per line, not per Year?

• Total aggregation of dimension

So, as stefan said is not a dimension but an expression.

Move it to the expression tab and I believe you dont need to do the aggregation.

• Total aggregation of dimension

Nima,

are u asking wath i post there some time ago?

Look my post