Straigh/Pivot Table issue

[#1]

Hi there,

I am a little bit confused and I need your advice asap.

Could you please see the attached qvw and help me reach the correct results?

I don't want to see doublications to the first column and I want to see the correct numbers (see text object). I have no idea what pivot does to the final results is it the Total Mode or something else?

Please check out the attachment and let me know if you need further info.

Regards,

Helen

[Anonymous]

Hello,

Basically these 2 are mathematically different. 

BR

Serhan

[#3]

Hi Eleni,

you just need go inside properties then choose number tab then in format pattern and change into this - ###0

see the below image

[#4]

Hi guys,

Thank you both for your answers but the problem is not the format.

Is that in the Straight table the dimension is doublicated....I want to only one row...........

[#5]

Anyboby???

[#6]

it will work on pivot table

[#7]

Unfortunately no,

because as you can see in the QV1.qvw when I am using Pivot table the results for the second and third expression are wrong when the second dimension is collapsed....

Any ideas?

[jerem1234 · Accepted solution]

It's because in your first table, you have a bunch of hidden dimensions (which affects the values you will get). I reduced it to find that Units FC1 14 changes the values once i removed it from the first table. Therefore for the second, I simply used the aggr function for the 2 expressions, aggregating them by [Profit center text] and [Units FC1 14]. I attached the qvw. The expressions were:

sum(aggr(sum([Sales FC1 14])-sum([Sales BU14]), [Profit center text], [Units FC1 14]))

sum(aggr(sum([Sales FC1 14]/[Units FC1 14]-[Sales BU14]/[Units BU14])*[Units FC1 14], [Profit center text], [Units FC1 14]))

Now you should double check the numbers to make sure that they are indeed the correct values that you want.

Hope this helps!

[#9]

That works!

Thanks a lot Jeremiah!