topic Set analysis to SUM values implied by current selections in QlikView

Set analysis to SUM values implied by current selections

Jason_Michaelides — Thu, 14 Apr 2011 15:08:17 GMT

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

Set analysis to SUM values implied by current selections

chris_johnson — Thu, 14 Apr 2011 15:30:33 GMT

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

Set analysis to SUM values implied by current selections

Jason_Michaelides — Thu, 14 Apr 2011 15:41:53 GMT

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

Set analysis to SUM values implied by current selections

chris_johnson — Thu, 14 Apr 2011 15:52:24 GMT

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?

Set analysis to SUM values implied by current selections

johnw — Fri, 15 Apr 2011 01:31:18 GMT

I'd think this (untested)?

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

Set analysis to SUM values implied by current selections

Jason_Michaelides — Fri, 15 Apr 2011 09:18:27 GMT

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