topic Re: Pivot Table Dimension (Sum) in QlikView

Pivot Table Dimension (Sum)

riceball — Wed, 13 May 2015 04:03:14 GMT

Hi,

I'm trying to build a pivot table with a single dimension (Product) on the left side and two dimensions (Type and Category) along the top.

However, the result of totalling up the sales through "partial sum" features was like this:

I would like my output to look like this instead:

Currently, I'm maintaining 2 different tables which is quite troublesome. I would like to consolidate my results into a single table like the one above.

Please help. Thanks!!

Re: Pivot Table Dimension (Sum)

rohit214 — Wed, 13 May 2015 04:16:16 GMT

Hi ,

I think insted of selecting show partial sum option you should use an expression for total

Aggr(Sum(sales),Product,Category)

Thanks

Rohit

Re: Pivot Table Dimension (Sum)

riceball — Wed, 13 May 2015 04:22:23 GMT

Hi,

I've tried the advance aggr method - Aggr(Sum(Sales),Product,Category) , but it still yield the same result

Thanks.

Re: Pivot Table Dimension (Sum)

rohit214 — Wed, 13 May 2015 04:30:07 GMT

then use

Sum(total<Product>Category) in total expression

Re: Pivot Table Dimension (Sum)

riceball — Wed, 13 May 2015 04:36:50 GMT

Hi,

It's not working too. I think changing the expression script doesn't help unless I assigned one expression for each category/type. But that is way to much work as I have over 30 types at my end.

Re: Pivot Table Dimension (Sum)

sunilkumarqv — Wed, 13 May 2015 04:45:39 GMT

check Category dimension partial sum in presentation

Re: Pivot Table Dimension (Sum)

riceball — Wed, 13 May 2015 05:30:53 GMT

Partial sum works, but the output is not what I wanted. It does not sum up each category respectively

Re: Pivot Table Dimension (Sum)

sunilkumarqv — Wed, 13 May 2015 07:20:27 GMT

Re: Pivot Table Dimension (Sum)

riceball — Wed, 13 May 2015 07:35:06 GMT

Hi,

Thanks for your help! But is it possible to aggr the type for the 2nd expression. Instead of listing "large","medium","small", I would like to have just the "Total" and then breakdown by Type. This enables me to see the total sales for each color

Re: Pivot Table Dimension (Sum)

mohammadkhatimi — Wed, 13 May 2015 07:41:38 GMT

Hie...

Check this ...

Re: Pivot Table Dimension (Sum)

sunilkumarqv — Wed, 13 May 2015 07:52:19 GMT

The best we can do here

you can map only required totals

Re: Pivot Table Dimension (Sum)

riceball — Wed, 13 May 2015 07:56:10 GMT

Hi,

Thanks for your suggestion. So far, this is the most workable method.

But the thing is that, the data I'm working on right now have over 30 Types and over 1000 categories. So, it's quite tedious for me to adopt your method (too many expressions). I'm looking at a single expression which can help to solve this problem (if possible)

Thank you.

Re: Pivot Table Dimension (Sum)

riceball — Wed, 13 May 2015 08:02:54 GMT

I see. I think that's a limitation for pivot table. I guess I will have to maintain two separate tables (shown in my attached qvw) if there is no solution to it.