topic Re: Help on bar chart with calculated dimensions / grouped dimensions in QlikView

Help on bar chart with calculated dimensions / grouped dimensions

Tue, 14 Aug 2012 02:44:44 GMT

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,

Re: Help on bar chart with calculated dimensions / grouped dimensions

Tue, 14 Aug 2012 02:52:05 GMT

I should add that I only have the QV personal edition, so if you could show what the sample script / expression text is, that'd be great.

Re: Help on bar chart with calculated dimensions / grouped dimensions

Tue, 14 Aug 2012 06:17:38 GMT

Dimension: Class(USD/10000000,1,'x',1)

Expression: Count(Supplier)

Hope it helps!

Re: Help on bar chart with calculated dimensions / grouped dimensions

Tue, 14 Aug 2012 06:36:52 GMT

Thank you, that's very helpful.

How can I sub-divide these classes further, e.g:

1. 0 <= $1m < 1

2. 1 <= $1m < 2

3. 2 <= $1m < 3

4. 3 <= $1m < 4

5. 4 <= $1m < 5

6. 5 <= $5m < 10

7.10 <= $40m < 50

8. $50m +

Also, can I set a condition that it only returns positive values (for example, there are a few entries that are refunds).

Thanks once again!

Re: Help on bar chart with calculated dimensions / grouped dimensions

Tue, 14 Aug 2012 07:17:50 GMT

I'm still working on to sub divide the classes as required.

If I have understood your questions correctly. I think you can use below in the Dimension to return only positive values.

=if(USD >0, Class(USD/10000000,1,'x',1))

Re: Help on bar chart with calculated dimensions / grouped dimensions

Tue, 14 Aug 2012 08:09:40 GMT

Thanks, that removed the negative values.

One more query is classing by:

Dimension: Class(USD/10000000,1,'x',1)

Expression: Count(Supplier)

groups each individual entry by its amount and then allocates that to the class, e.g. each single small transaction for $1000 is grouped under <$1m.

How can I accumulate all transactions by supplier and then split those totals into the same classes above? i.e. A supplier whose transactions are only for small amounts but which total to a much larger figure (e.g. $30m) would be classified as one count in the $10m <= $40m < $50m bracket.

Re: Help on bar chart with calculated dimensions / grouped dimensions

jonathandienst — Tue, 14 Aug 2012 08:29:54 GMT

For more flexible bands/classes, you could also do this in script like this:

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;

I made the band values duals so that they sort properly. They will display the text portion, and sort by the numeric portion. The RangeMax excludes all refunds from the calculation.

This could also be done using intervalmatch rather than a nested If. This would allow the bands to be adjusted without modifying the script; simply modify the From/To values in the intervalmatch table. Search the forum/manual for intervalmatch for more information if you are not familiar with intervalmatch.

Hope that helps

Jonathan

Re: Help on bar chart with calculated dimensions / grouped dimensions

Tue, 14 Aug 2012 09:24:46 GMT

Thanks Jonathan,

I can't seem to get this script to work. The script is very basic at present: just two separate excel files concatenated together.

How can I rephrase the 'Load Supplier' line to simply pull the [Supplier] field listed in the concatenated tables?

Apologies for my lack of knowledge in this.

Re: Help on bar chart with calculated dimensions / grouped dimensions

Wed, 15 Aug 2012 03:48:56 GMT

Any more ideas on how to approach this would be gratefully appreciated!

Re: Help on bar chart with calculated dimensions / grouped dimensions

jonathandienst — Wed, 15 Aug 2012 06:07:49 GMT

If you post your script here, then I can show you.

Regards

Jonathan

Re: Help on bar chart with calculated dimensions / grouped dimensions

Wed, 15 Aug 2012 06:15:30 GMT

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

Re: Help on bar chart with calculated dimensions / grouped dimensions

jonathandienst — Wed, 15 Aug 2012 06:27:02 GMT

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

Re: Help on bar chart with calculated dimensions / grouped dimensions

Wed, 15 Aug 2012 06:33:58 GMT

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,

Re: Help on bar chart with calculated dimensions / grouped dimensions

jonathandienst — Thu, 16 Aug 2012 09:03:30 GMT

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