Set Modifier Syntax

[#1]

Hi All,

I want to add an expression to a straight table, where the definition needs to refer to more than just one field in the expression (apologies if I don't have the terminolgy correct).

The initial expression works fine:-

=sum({$<[BRANCH-CODE]={'DOBCROSS'}>}[WEEKLY-COSTOFGOODS])

I now wish to expand this so that it refers to an additional set of values which are contained within another column of the same straight table. :-

=sum({$<[BRANCH-CODE]={'DOBCROSS'},[ITEM-NUMBER]=column(2)>}[WEEKLY-QTY-SOLD])

The straight table shows the top 20 values based on the values in the first column which are the weekly cost of goods sold.  I now wish to see the weekly qty of goods sold in the same table, but since these fields are different, I don't see the correct quantities sold,and the totals are for all items sold, not just those that are in the top 20.  Hence, why I am trying to reference the item numbers (primary key) for those in the top 20 to only show the sold quantites for those items.

Hopefully someone can point me in the right direction.

Thanks

[stephencredmond]

Hi,

Need a little more information.  If possible a sample doc would be really helpful.

Top 20 what?

Regards,

Stephen

[#3]

If nothing else, can anyone point me in the direction of a good source for Set Modifiers and expressions.  I've used the reference guide but its not helping me that much.

All I really need to know is how to use multipe modifiers in the same expression - all I get told is there is a syntax error and I have no idea why, as it looks correct to me based on the reference material I have.

Thanks

[#4]

I'm not sure how to supply an example document due to the size of data I'm working with, sorry. However I will try to explain a bit more about what I am attempting to achieve.

Straight table needs to show top 20 items in each deparment by their Gross Margin.

The dimensions in the table are Department and then Item.

The first expression in the straight table is the calculation for the Gross Margin.

The subsequent four expressions are the Gross Margin buy using Set Modifiers for each of the companies branches.

This works perfectly as the straight table is restricted to show the top 20 values based upon the first expression, which is the overal Gross Margin.

The issue is that the client wished to show several other fields of information, for example, Quantity Sold and Value of Good Sold, within the same table, and these values should only be for the items which appear in the top 20.

If I just add new expressions for these, the values shown aren't related to the items within the top 20,  My solution to this, was to add another field into the straight table that shows the unique item number.  Then, using set analysis, I want to link to the item number column in the straight table to only show the values of Quantity Sold, and Value of Goods sold, for those items in the top 20 based upon Gross Margin.

I hope this gives a bit more information and thus someone can point me in the right direction.

Thanks

[perumal_41]

Hi

Try This Expression

=sum({$<[BRANCH-CODE]={'DOBCROSS'},[ITEM-NUMBER]=[column(2)]>}[WEEKLY-QTY-SOLD])

Regards

Perumal A

[Jason_Michaelides]

Are you able to make some selections to limit the data set, then Reduce Data and post an example?

[stephencredmond]

Hi Trevor,

How are you restricting the Product to top 20?

If I was doing it (prior to v11), I would be using an AGGR in a calculated dimension something like this:

=If(Aggr(Rank(Sum(Margin)), ProductName) <= 20, ProductName, Null())

Then set that dimension to suppress null values.

This way, I only get the items, for each Department, that are in the top 20 for that department.  Any other expressions that I add will be only for those items.

Regards,

Stephen

[stephencredmond]

Hi Perumal,

This will not work.

column(2) refers to a calculation - the result of an expression.  Not an item number.  Adding square barckets doesn't make it any better as this is still syntactically incorrect.

The best way to remember what goes in a set is that it is a set of selections.  The values in the set (surrounded by { } ) will be, basically, anything that you can type into a search dialog of a List Box.

Here are examples of valid sets:

Sum( {<[ITEM-NUMBER]={'12345'}>}  Sales)

Sum( {<[BRANCH-CODE]={'XYZ'}, [ITEM-NUMBER]={'12345'}>} Sales)

Sum( {<[BRANCH-CODE]={'XYZ', 'A?C'}, [ITEM-NUMBER]={'12345', '235*'}>} Sales)

Sum( {<[BRANCH-CODE]={'XYZ', 'A?C'}, [ITEM-NUMBER]={'=Sum(Sales)>50000'}>} Sales)

Sum( {<[BRANCH-CODE]={'XYZ', 'A?C'}, [ORDER-DATE]={">=01/01/2010<=31/12/2011"}>} Sales)

As shown in my previous response, you do not always need to go to sets to come up with a solution.

Regards,

Stephen

[#9]

Hi Stephen,

Yes, working in V11 so am using the inbuilt restriction to top 20 on the Dimension Limits tab.  I assume that this functionality is put there for quick and easy solutions to more simplistic data views, and that perhaps in this case I'm doing something a little bit more involved that requires aggregation functions - this is new to me, so will look at your suggestion and also read the reference guide for more info.

Thanks for your quick response.

Trevor

[stephencredmond]

Hi Trevor,

If you are using the v11 functionality to restrict the 2nd dimension of the chart (Item) to the top 20 values of the first expression, then any other expressions show will only be relevant to those 20 items.

Are you saying that you are then seeing more than 20 items?

Regards,

Stephen