Set Analysis

[JohannEls]

Hi,

I would like to base my Sales figure on two fields within my Set Modifier. The trick that I battle with is to get the two fields in my Set Modifier working like an "OR" as in an IF Statement. Thus if either the Region = 'X' OR the MarketSegment = 'Y' in my Set Modifier, then it should sum the Sales value. How would I do this? Probably very simple... Thanks

Johann

[Miguel_Angel_Baeyens · Accepted solution]

Hello Johann,

I'd use two set modifiers, something like (untested)

Sum({< Region = {'X'} > + < MarketSegment = {'Y'} >} Sales)

[JohannEls]

Hi,

Thanks for the reply. I tried it, but it just brought through all the data ignoring all set modifiers.

Johann

Hi,

It works great! I found my mistake - MarketSegment had a space in the name (Market Segment) - I just had to put it in square brackets.

A Union is thus the perfect sollution to an "OR" in Set Analysis!

Thanks

Johann

[#4]

Hi,

Two ways I'd look at it - either add an extra column with a Y/N flag that returns your if statement requirements or would using Bookmarks work?

If you create a Bookmark that selects Region = 'X' and MarketSegment = 'Y' then that should (if I've understood them correctly) give you the results you need. Not sure if it's different in version 9 - I'm running version 8.5 at the moment (but soon to upgrade).

Hope you get it sorted!

Cheers,

Emma

[JohannEls]

Hi Emma,

Using wild card characters in my field filters, make it a bit more complex using Bookmarks - any idea of how I should treat this. (Perhaps with variables - but how exactly?)

I have included the actual Set Analysis code witten, with the correct fieldnames. I would like to include both of these two result sets in my end result - not duplicating the values.

Sum({$<CUSTOMER={'*CASH SALE*'},PartRev={'R'}>}Amt)-Sum({$<CUSTOMER={'*CASH SALE*'},PartRev={'R'}>}Discount)
Sum({$<MARKET SEGMENT={'*cash*'},PartRev={'R'}>}Amt)-Sum({$<MARKET SEGMENT={'*cash*'},PartRev={'R'}>}Discount)

Thanks

Johann

[#6]

Hi,

I think you can use wild cards in the bookmark so this shouldn't create a problem so you could try that, I find it helpful to have the current selections box showing while I'm creating some of these things as it's simpler to make sure your bookmark has exactly what you want in it and nothing else. Otherwise I'd probably find it easier to clean up the data as you load if this is part of the problem. Where there is *cash* in the MARKET SEGMENT is there ever going to be something other than *CASH SALE* in the CUSTOMER field? Alternatively go down the additional field route.

In the above example you should be able to create a bookmark that selects

CUSTOMER = *CASH SALES*

PartRev = R

MARKET SEGMENT = *cash*

Then your set analysis statement just references the bookmark name.

Cheers,

Emma