13 Replies Latest reply: Aug 16, 2012 5:03 AM by Jonathan Dienst RSS

    Help on bar chart with calculated dimensions / grouped dimensions

      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

          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

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

            Expression: Count(Supplier)

             

            Hope it helps!

            • Re: Help on bar chart with calculated dimensions / grouped dimensions
              Jonathan Dienst

              Hi

               

              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

                  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

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

                      • Re: Help on bar chart with calculated dimensions / grouped dimensions
                        Jonathan Dienst

                        Hi

                         

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

                         

                        Regards

                        Jonathan

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

                            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