Set analysis to SUM values implied by current selections

[Jason_Michaelides]

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

[chris_johnson]

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]

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]

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 · Accepted solution]

I'd think this (untested)?

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

[Jason_Michaelides]

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