is this valid syntax for a set expression ?

[#1]

sum({$ <Sales.Store_No = {324} Sales.Item_No = {$(GetFieldSelections(ITEM_NO))} >} Sales.IsValidSale)

it is returning 0 for every row in my pivot table even when i select 1 ITEM_NO that i know has sales, of course it works if I hard code an Item_no in a similar way to store 324.

What is the correct way to do this, the sales table is not linked to my data table so what im actually after in this case is Sales.item_no = Mypivot.currentrow.ITEM_NO ?

ColinR

[#2]

You need to separate multiple fields in your set expression with commas:

sum({$<Sales.Store_No={324}, Sales.Item_No={$(=GetFieldSelections(ITEM_NO))} >} Sales.IsValidSale)

EDIT: I also think you need an equals sign to start your dollar sign expansion.

[#3]

you are right about the = sign, but help sugggests getfieldselections() is comma seperated by default

tried your prev suggestion of expression as column header and the getfields is evaluating to {} for some reason

[#4]

Did you make a selection in the ITEM_NO field? If no selections are made, it returns null.

I meant you needed a comma between the two fields.

...Store_No={324} , Sales.Item_No...

The GetFieldSelections should evaluate in a Text Object. Check this in a Text Object:

=GetFieldSelections(ITEM_NO)

I think the expression looks fine though. Are both Store_No and Item_No numeric fields? They aren't strings, are they?

[#5]

Ah that comma will explain why the syntax didnt validate in the expression builder

Cheers

[johnw]

Worth noting is that while set analysis will let you get away with something like MyField={MyValue}, it will NOT let you get away with MyField={My Value}. If there can be any whitespace in your values, they need to be enclosed in single quotes, so MyField={'My Value'}. You may also sometimes see people code MyField={"My Value"}. While this WILL work, it is NOT specifying a literal, but rather a search string. Search string "My Value" will only match literal 'My Value', which is why it still works, but I would recommend using literals when you want literals, and search strings only when you actually want to match multiple values.

[johnw]

Oh, something even more basic to simplify the expression and perhaps improve performance. A field IS an element set. So you should be able to do this:

sum({<Sales.Store_No={324}, Sales.Item_No=ITEM_NO>} Sales.IsValidSale)

[#8]

Interesting,

Though i went for a similar syntax at some point in the process:

sum({<Sales.Store_No={324}, Sales.Item_No={ITEM_NO}>} Sales.IsValidSale)

There does not seem to be much in the docs about the Use and application of Set Analysis

As Ever I appreciate your Input

ColinR