topic Re: Straigh/Pivot Table issue in QlikView

Straigh/Pivot Table issue

Thu, 11 Dec 2014 15:56:10 GMT

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

Re: Straigh/Pivot Table issue

Anonymous — Thu, 11 Dec 2014 16:02:52 GMT

Hello,

Basically these 2 are mathematically different.

Serhan

Re: Straigh/Pivot Table issue

Thu, 11 Dec 2014 16:08:20 GMT

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

Re: Straigh/Pivot Table issue

Fri, 12 Dec 2014 08:34:56 GMT

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...........

Re: Straigh/Pivot Table issue

Fri, 12 Dec 2014 10:41:31 GMT

Anyboby???

Re: Straigh/Pivot Table issue

Fri, 12 Dec 2014 12:38:10 GMT

it will work on pivot table

Re: Straigh/Pivot Table issue

Fri, 12 Dec 2014 15:05:35 GMT

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?

Re: Straigh/Pivot Table issue

jerem1234 — Fri, 12 Dec 2014 15:17:46 GMT

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!

Re: Straigh/Pivot Table issue

Fri, 12 Dec 2014 15:25:27 GMT

That works!

Thanks a lot Jeremiah!