Post reply

Change aggregation using custom rollups

  • September 19, 2005 at 11:39 pm

    #165491

    Hi

    I am trying to change the aggregation on a dimension structured as

    below.

    All

      -> Group 1

        -> Item 1

        -> Item 2

      -> Group 2

        -> Item 2

        -> Item 3

    I need the All level to calculate the distinct sum of items so that

    Item 2 is only included once.

    I have tried the following MDX in the All Member Formula field but have

    not been able to get the correct result.

    sum(distinct({[Dimension].[Item Level].Members}))

    Any help would be appreciated

    Thanks

    Gavin.

  • September 21, 2005 at 9:17 pm

    #592067

    Hi Gavin,

    You may want to try using unary operators where you specify against each member how their rollup should occur (ie customer rollup).  From memory the tilde (~) means don't roll me up, so using you example the operators could look like

    All

      -> Group 1

        -> Item 1  +

        -> Item 2  +

      -> Group 2

        -> Item 2  ~

        -> Item 3  +

    Note that I arbitrarily chose that Item 2 in Group 1 be the one that rolls up.  This will/would cause you issues when viewing at the group level as Group 2's total won't incldue Item 2 if you set the rollup to be 'none'.

    Is there a natural relationship/hierarchy between these items, or perhaps they could work in seperate dimensions and you can view the "sales" (ie whatever your measure is) of Groups versus Items by nesting the two dimensions?

    HTH,

    Steve.

  • September 21, 2005 at 10:02 pm

    #592072

    Thanks for the response Steve,

    The relationship I have is one of a customer belonging to one or more chains.  This is usually a local chain and a national chain.  Both chains need to be compared to each other with the customers sales attributing to both.

    So unfortunately even adding an additional level above the groups still leaves the came problem on the total across all chains.

    Thanks

    Gavin.

     

  • July 6, 2010 at 12:32 am

    #1188940

    Gavin,

    maybe you can work around this by setting the Top Level dimension entries of all but one of the chains to "~".

    This will only show the sums of the one chain (assuming that all your customers belong to that chain).

    If there is no chain that includes every customer you may want to introduce a virtual chain that includes all customers. Then, you set the unary operator of all real chains to "~" and you will get the right "all" value.

    However, this will leave you with an additional item in your hierarchy display...

    Guenter

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply