Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
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
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
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,
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