Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Excluded dimensions in Pivot Table

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Excluded dimensions in Pivot Table

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

Alessandro FurtadoImage.png

6 Replies
Not applicable

Re: Excluded dimensions in Pivot Table

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

Not applicable

Re: Excluded dimensions in Pivot Table

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

whiteline
Honored Contributor II

Re: Excluded dimensions in Pivot Table

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

Not applicable

Re: Excluded dimensions in Pivot Table

Hi,

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

Linda

MVP
MVP

Re: Excluded dimensions in Pivot Table

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

Alessandro FurtadoImage.png

Not applicable

Re: Excluded dimensions in Pivot Table

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

Community Browser