Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Jason_Michaelides
Luminary Alumni
Luminary Alumni

Set analysis to SUM values implied by current selections

Hi all,

I'm close to this I think but can't get the syntax right - hoping someone can help!

Say I have 3 list boxes:

1. Product
2. Colour
3. Size

If I select a particular Product (e.g. Shoe123) I can see (in white) all the Colours and Sizes that product has been sold in. I can see the total sales for my selections with SUM(Sales). What I want to see is the TOTAL sales for all the Colours and Sizes that Shoe123 has been sold in. This should be the same value as "selecting possible" from Colour and Size then deselecting Product.

Pretty sure I need to use the P() element function...I think!

Thanks in advance,

Jason

1 Solution

Accepted Solutions
johnw
Champion III
Champion III

I'd think this (untested)?

sum({<Product=,Colour=P(),Size=P()>} Sales)

View solution in original post

5 Replies
chris_johnson
Creator III
Creator III

Hi Jason,

Yes you do! I had a play with it on the Developer 3 course but haven't really used it but what you have described is exactly what it can be used for. Also there is the E() function for selecting excluded records too.

I know that really doesn't solve your problem but I thought I'd give you a quick message to say you're on the right lines.

Chris

Jason_Michaelides
Luminary Alumni
Luminary Alumni
Author

I was also on the Dev III course and have been looking at the workbook. The problem is the examples deal with explicitly making a selection within the set expression P() to get the implicit values. I want to use the values implied by all the current selections.

I tried

SUM({$<Colour=(P()),Product=>} Sales)
and it's frustratingly almost right...

chris_johnson
Creator III
Creator III

I'm trying to work it out from the book as well funnily enough.

Maybe:

SUM({$<Colour=(P()), Product={'*'}>} Sales) * SUM({$<Size=(P()), Product={'*'}>} Sales)

The first bit for your possible colours and the second bit to combine it with the possible sizes?

johnw
Champion III
Champion III

I'd think this (untested)?

sum({<Product=,Colour=P(),Size=P()>} Sales)

Jason_Michaelides
Luminary Alumni
Luminary Alumni
Author

Hi John,

Late last night I solved it with the same answer. Actually I wanted to exclude all other selections so I used:

SUM({1<Colour=P(),Size=P()>} Sales)

Thanks again all - this community is fantastic.

Jason