Set analysis sum of others question

[DavidFoster1]

Hi all.

I have a data set with customer sales by branch of a retail company.

The report requirement is provide the following

Branch	Customer	Local Sales	Other Branches
A	John Smith	£1000	£2000
B	John Smith	£1500	£1500
C	John Smith	£500	£2500

I then need to be able to filter down to a single Branch record and the values remain the same.

The problem is how to achieve the 'Other Branches' total. My attempts at using set analysis to use the Excluded branches caused the Branch dimension values to appear even though they were filtered.

Any help appreciated.

[MK_QSL]

Considering that you have below data...

Sales:

Load * Inline

[

  Branch, Customer, Sales

  A, C1, 1000

  A, C2, 500

  A, C3, 1500

  A, C4, 2000

  B, C5, 1200

  B, C6, 1400

  B, C7, 1600

  C, C8, 800

  C, C9, 700

];

Create a Straight Table

Dimension

Branch

Customer

Expressions

1) SUM(Sales)

2) SUM(ALL Sales) - SUM(TOTAL <Branch> Sales)

3) SUM(ALL Sales) - SUM(Sales)

[Anonymous]

SUM(TOTAL <Customer> Sales) - SUM(Sales)

[DavidFoster1]

No good. The ALL on its own sums up all values in the datamodel.

I have got closer with:

SUM(ALL <CUSTOMER> SALES) - Sum(TOTAL <CUSTOMER> SALES)

But this causes the straight table to ignore any filters on BRANCH

[DavidFoster1]

TOTAL is no good as its scope is only the currently filtered data (and doing {1} TOTAL is effectively ALL)

[Anonymous]

This expression is basicaly TOTAL except <this, andthis>, so just add filters you want.

[MK_QSL]

Create a Straight Table

Dimension

BRANCH

CUSTOMER

Expressions

1) SUM(SALES)

2) SUM(TOTAL <CUSTOMER> SALES)-SUM(SALES)

[DavidFoster1]

Do you mean set analysis?

[DavidFoster1 · Accepted solution]

Got it! Proper deep dark stuff!

AGGR(NODISTINCT SUM({1} SALES),CUSTOMER) - SUM(SALES)

[johnca]

Here's how I would do it...

DATA:

Load * Inline [ BRANCH,CUSTOMER,SALES

A,JOHN,1000

A,JOE,1500

A,JANE,500

B,JOHN,2000

B,JOE,2500

B,JANE,500

C,JOHN,3000

C,JOE,0

C,JANE,1000,

];

SALES_SUM:   

Load

CUSTOMER

Sum(SALES) as SALES_SUM

Resident DATA

Group By CUSTOMER;

Then in your chart;

Dimension 1: BRANCH

Dimension 2: CUSTOMER

Expression 1: Sum(Sales)

Expression 2: Sum(SALES_SUM) - Sum(SALES)

HTH,

John

[MK_QSL]

I know this one is old.. but was looking for something and reached here..

the alternate solution may be..

=SUM(Total <CUSTOMER> Aggr(SUM({<BRANCH>}SALES),CUSTOMER)) - SUM(SALES)