Help on bar chart with calculated dimensions / grouped dimensions

[#1]

Hello,

I have a very long list of transactions with different suppliers. I want to create a bar chart that groups the suppliers by the sum of the total annual spend and shows how many suppliers there are in each of these brackets. Please see the attached excel file as a very basic example of this.

Many thanks for any help you can offer on this.

Regards,

[#2]

Thanks Jonathan, here it is:

QUOTE"

SET ThousandSep=',';SET DecimalSep='.';SET MoneyThousandSep=',';SET MoneyDecimalSep='.';SET MoneyFormat='$#,##0.00;($#,##0.00)';SET TimeFormat='h:mm:ss TT';SET DateFormat='DD/MM/YYYY';SET TimestampFormat='DD/MM/YYYY h:mm:ss[.fff] TT';SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

[Asia (ex SGP)]:

LOAD Year,
     Period,
     Region,
     Country,
     [Sorted Supplier Group],
     [Primary Dimension],
     [Secondary Dimension],
     USD

FROM
[Asia Data - ex SGP (raw data).xls]
(biff, embedded labels, table is [Asia Raw Data$]);

SGP:

Concatenate LOAD Year,
     Period,
     Region,
     Country,
     [Sorted Supplier Group],
     [Primary Dimension],
     [Secondary Dimension],
     USD

FROM
[Asia Data - SGP (raw data).xls]
(biff, embedded labels, table is [Singapore Raw Data$]);

SaleBySupplier:

LOAD *,
          If(SupplierTotal <= 1000000,           Dual('<1m', 0),
          If(SupplierTotal <= 2000000,           Dual('1m-2m', 1),
          If(SupplierTotal <= 3000000,           Dual('2m-3m', 2),
          If(SupplierTotal <= 4000000,           Dual('3m-4m', 3),
          If(SupplierTotal <= 5000000,           Dual('4m-5m', 4),
          If(SupplierTotal <= 10000000,          Dual('5m-10m', 5),
          If(SupplierTotal <= 50000000,          Dual('10m-50m', 6),
                                                 Dual('>50m', 7)))))))) As Band
;LOAD Supplier,
          Sum(RangeMax(0, Amount)) As SupplierTotal

Resident Sales;

"UNQUOTE

[jonathandienst]

Hi

Assuming by 'supplier', you meant [Sorted Supplier Group], then just replace the reference to Supplier and Amount in the last LOAD statement  with the correct field names

;LOAD [Sorted Supplier Group],

          Sum(RangeMax(0, USD)) As SupplierTotal

Resident Sales;

Regards

Jonathan

[#4]

Thanks Jonathan,

I changed it accordingly to:

LOAD [Sorted Supplier Group],
          Sum(RangeMax(0, USD)) As SupplierTotal 

Resident Sales;

however I get an error message that 'Table not found: LOAD [Sorted Supplier Group]...'.

Regards,

[jonathandienst]

One more thing to fix:

The label [Asia (ex SGP)] covers both source loads (as the second load is concatenated onto the first). This label, and the Resident statement need to be the same. I suggest that you change the label to [Asia] (because the final table will include SGP), and change the "Resident Sales" to "Resident [Asia]"

This is what happens when you cant run the script to test it

Regards

Jonathan