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

[#2]

Hi Stephen,

The values themselves look ok, it is that the totals for each department, I would have assumed, would show the sum of the quantity sold for the 20 items shown above, but it seems to show the total for all items sold in the department.

Is this expected behaviour or is there something I'm not doing or doing wrong, or that I'm wrong in my assumption?

Thanks

Trevor

[stephencredmond]

Hi Trevor,

The default total in a straight table is the "Expression Total".  You probably want to change the total mode to Sum of Rows.

Regards,

Stephen

[#4]

Hi Stephen,

Thanks for this, but when i change this on the Quantity column it appears to have no affect at all.  Do I have to do this for each expression in the table, or should it work just on the selected column?

Thanks

Trevor

[stephencredmond]

Hi Trevor,

You will need to do this on every column that you need to do it on.

The "Expression Default" would just calculate the expression, e.g. Sum(Value), as if there were no dimensions.

Actually, my method with the Aggr would work better in that instance because every other item would have "Null" as its value in the chart and, with Nulls suppressed, the value would calculate correctly.

Regards,

Stephen

[#6]

Hi Stephen,

I've just added a word document that shows a screen shot of the straight table in question - not sure if this will be more helpful, but hopefully it displays the issue with the totals that I'm having.

Thanks

Trevor

[stephencredmond]

Something to do with the totals from the Dimension limitations.

Try the AGGR approach.

Regards,

Stephen