Pivot Table

[tmumaw]

Okay group here is an easy one for you all.  I have pivot table that is broken down by GL Hier 3 I want to compare it to gross sales.  How do I get the pivot table to ignore the dimension and just put in total gross sales.  I have created a summary table with just gross sales by profit center and year.

[vishsaggi]

What is the expression you used and what is your expected output in Gross Sales column. ?

May be like:

= Sum({<GLHeir3=>} Aggr(Sum({<GLHeir3=>}Sales), ProfitCenter, Year))

[tmumaw]

I created a link key made up of Profit Center and Date and then I summed the total gross sales amount.  For some reason the Gross Sales amount is still blank. GLPCA is the detail table.

[ahaahaaha]

You need to analyze the load script. Something is wrong with joining the SalesVIP table. I think, to help you, need the fragment of your source data and script code.

[tmumaw]

This calculation did not work.  I am trying to find out what expenses are to gross sales.  The column labeled Gross sales should be the same on every line and ignore the dimension GL Hier 3.  The second image shows gross sales only showing up in one GL Hier 3 row.  However I need it to show up in all rows to do the calculation % to Gross

[tmumaw · Accepted solution]

Andrey I need to get Gross Sales on every line to do the % to gross calculation.  Any ideas?  Fixed had to use the following:

sum(TOTAL <FISYR> {<[GL Hier 3] = {'Gross Sales'}>}[In company code currency_HSL]  * -1)

[ahaahaaha]

May be approach to the solution as in attached file.

[tmumaw]

Thanks for your help Andrey.  Have a great day.

[chaper]

Did you tried with something  like this Sum({<GLHeir3=>}Total Sales)