topic Re: Excluded dimensions in Pivot Table in QlikView

Excluded dimensions in Pivot Table

Fri, 22 Nov 2013 11:07:38 GMT

Hello,

I have a series of CustomerIDs and each of them is linked to several PartnerIDs along with the related Sales by Product. I have a pivot table where my first dimension is CustomerID and as a second dimension I need to show ALL the Partners that have transacted with these Customers in the past regardless of any other selection. I appreciate the sum(Sales) expression would return a '0' in such cases and that's fine as long as all the Partners are visible. On the other hand, CustomerIDs showing in the chart should be indeed driven by the selections.

I've attempted several expressions for the Partners but none worked, not even the one below which looks fine to me:

=aggr(if(sum({1} Sales)>0,PartnerID),PartnerID,CustomerID)

Can somebody help please?

I've attached a sample document.

Thanks,

Linda

Re: Excluded dimensions in Pivot Table

Fri, 22 Nov 2013 14:36:44 GMT

I didn't get the question correctly, Please elaborate the problem in details

Re: Excluded dimensions in Pivot Table

Fri, 22 Nov 2013 16:03:27 GMT

Hi,

Basically, looking at the sample document, if you select CustomerID 5562345 the pivot shows that this Customer is connected to 5 Partners. The issue is that if I make a further selection and pick for example Product B, which only Partner A sold to the Customer, all other Partners disappear. What I need is for all 5 Partners to keep showing even if they haven't sold anything to Customer within the specific selection.

Hope this clarifies.

Thanks,

Linda

Re: Excluded dimensions in Pivot Table

whiteline — Fri, 22 Nov 2013 20:23:27 GMT

Hi.

First remove your calculated dimensions, use simple PartnerID and PartnerName as second and third dimesnions.

Then add something to the Sum(sales) that is not null for other (not selected) PartnerNames and equals 1 for selected ones.

For example, =sum(Sales)*Count(distinct {<PartnerName=>} PartnerName).

Depending on your data model you can omit 'distinct' or try something similar.

There is also something to do with a total...

Re: Excluded dimensions in Pivot Table

Sun, 24 Nov 2013 11:35:08 GMT

Hi,

Thanks for the suggestion. I tried it but nothing changed... Partners excluded by the selections disappear anyway.

Linda

Re: Excluded dimensions in Pivot Table

afurtado — Sun, 24 Nov 2013 11:56:37 GMT

Hi. I just added a new expression. Maybe can help you.

Alessandro Furtado

Re: Excluded dimensions in Pivot Table

Sun, 24 Nov 2013 13:21:15 GMT

Thanks for the trick! It's so simple it's genius!