Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

jason_michaelid
Honored Contributor II

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

Tags (2)
1 Solution

Accepted Solutions
MVP
MVP

Set analysis to SUM values implied by current selections

I'd think this (untested)?

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

5 Replies
chris_johnson
Contributor II

Set analysis to SUM values implied by current selections

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_michaelid
Honored Contributor II

Set analysis to SUM values implied by current selections

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
Contributor II

Set analysis to SUM values implied by current selections

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?

MVP
MVP

Set analysis to SUM values implied by current selections

I'd think this (untested)?

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

jason_michaelid
Honored Contributor II

Set analysis to SUM values implied by current selections

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

Community Browser