Show excluded values in straight table

[#1]

Hello,

I have a series of CustomerIDs and each of them is linked to several PartnerIDs along with the related Sales by Product. Out of these PartnerIDs I only want to show the one that matches another field called AssignedPartnerID - there is only one possible AssignedPartnerID per CustomerID. In my straight table I got as far as showing the correct PartnerID when this is not excluded by the selections. However, I need the chart to always show that specific Partner and the related total Sales regardless of any selection whatsoever in the document and even if the Customer hasn't purchased anything at all in the selected Product.

The problem at the moment is that the PartnerID and Total Sales fields will show empty in the straight table if I select a Product that PartnerID 5290 hasn't sold or if I select a different Partner ID.

Can anybody help? I'm really struggling with this one

I've attached a sample document with one CustomerID and several PartnerIDs and Products.

Thanks!

[#2]

I cannot use the QVW attached because I have the Personal Edition.

You will solve that problem by using the Set Analysis (see a doc I have written on it : http://community.qlik.com/docs/DOC-4951

or see the community)

You will need to reset the Product dimension to all, or perhaps Partner ....

sum ( {<Product = >} Sales)

Fabrice

[#3]

Hi Frabrice,

Thanks for your response.

Have already tried several combinations with set analysis but was unable to find one that does the job.

For those who can open the document, I've made a couple of changes and attached version 2 of the sample document: it's ok for the chart not to show anything when Products with no sales overall are selected - i.e. Product A and Product F - but, for example, I'd still need to see Partner 5290 and Total Sales $1,141,378.63 when Product H is selected although this specific product has in fact been purchased by the Customer only from Partner 6360. .

Any ideas?

Thanks,

Linda

[Jason_Michaelides]

Hi Linda,

I've achieved this for you without a complicated expression which I believe you need if you do this in the UI.  Instead, I have added a bit in your script:

If(PartnerID = AssignedPartnerID,1,0)

AS

AssignedPartnerFlag

This relies on your rule above that states "there is only one possible AssignedPartnerID per CustomerID".  If the PartnerID for this sale is the Assigned partner, then flag 1, otherwise 0.  Then, as a simple expression:

Sum({1} Sales*AssignedPartnerFlag)

Will sum all sales for the assigned partner only as sales to other partners will be multiplied by 0.

Hope this helps,

Jason

[#5]

Hi Jason,

Many thanks for your very clever suggestion. I use flags a lot but didn't think of using them for this case. In reality, my script is much more complex than the one I've attached but I believe I can make some tweaks to accomodate for your additional bit.

However, this only solves part of my problem. In the real thing I also have to show the Partner Name related to the Assigned PartnerID, the most recent quarter they transacted in and the nr of open deals they have. What do I do to make sure these don't disappear when excluded by the selections? I've now added the fields in the script in a way that reflects how the data is distributed in the real document - I didn't include these fields in the first place because I thought a suggestion on how to get the PartnerID field to keep showing would have solved this bit too. Please note I cannot merge or link the data tables together in any other way.

BTW, I've added the relevant bits to your straight table too but of course they only work when not excluded by the selections.

Can you help?

Thanks,

Linda

[Jason_Michaelides · Accepted solution]

Hi Linda,

The beauty of this flag approach is it can be used all over and avoid the need for long Aggr() expressions.  Basically, add the most basic of set analysis to all your expressions:

{1<AssignedPartnerFlag={1}>}

See attached.

On a separate note, I don't understand why you are adding an If() statement to the sum of sales:

if(sum(Sales)>0, Sum({1} Sales*AssignedPartnerFlag))

In the presentation tab, "Suppress Zero Values" will ensure only rows with a value are included (as well as rows included by other expressions of course).

Hope this helps,

Jason

[#7]

Hi Jason,

Thank you so much! This is perfect!

The reason I'm using that if() statement, which I've now changed to if(sum({1 < Product = p(Product)>} Sales)>0 is that I want the entire row to disappear if the Customer hasn't purchased the selected product from any Partner. "Suppress Zero Values" won't allow me to do that in this case.

Thanks again

Linda

[Jason_Michaelides]

Hi Linda,

Glad it worked OK.

Regarding your new If() statement, I think you may find the row doesn't disappear, just that the sum of sales will  zero. This is due to the {1<>} in the other expressions which will return a value for them.

Also, Sum({1<Product = P(Product)>} Sales*AssignedPartnerFlag) should do the same and avoid the If() statement.

Jason

[#9]

Hi Jason,

Apparently it does disappear indeed. However, I will try your suggestion as well.

Linda

PS

Great avatar!!!

[Jason_Michaelides]

Interesting....ah well.

Avatar was my leaving costume from my Haymarket colleagues!