15 Replies Latest reply: Feb 9, 2012 9:58 AM by Stephen Redmond RSS

    Set Modifier Syntax

      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:-





      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. :-






      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.



        • Set Modifier Syntax
          Stephen Redmond



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


          Top 20 what?







          • Set Modifier Syntax

            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.



              • Re: Set Modifier Syntax

                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.



                  • Set Modifier Syntax
                    Jason Michaelides

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

                    • Re: Set Modifier Syntax
                      Stephen Redmond

                      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.






                        • Re: Set Modifier Syntax

                          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.



                    • Re: Set Modifier Syntax
                      Perumal Ayyappan


                      Try This Expression




                      Perumal A

                        • Re: Set Modifier Syntax
                          Stephen Redmond

                          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.