21 Replies Latest reply: Jun 29, 2011 10:53 AM by Miguel Angel Baeyens de Arce RSS

    fields as a chart's dimension

      hi guys,

       

      is it possible to have fields as dimensions of a bar chart?

       

      for e.g. i have sales, cost, netrevenue and location of table1

       

      the dimensions would be field1, field2 and field3 while the expression would be the locationfield

       

      so i can compare values of field1, field2 and field3 easily since their bars are side by side

       

      location1 location2 location3    location1 location2 location3     location1 location2 location3

                         sales                                          cost                                                     netrevenue

       

      location1, location2 and location3 are values of the field location

       

      i know that it is simple to have sales, cost and netrevenue as the expression with location as the dimension but is it possible in the scenario i have described above?

        • fields as a chart's dimension

          Hi,

           

          I solve such a problem with different loading the data.

           

          In that case:

           

          Tablename:

          LOAD

               location,

               'Sales' AS type

          RESIDENT table1

          WHERE field = sales;

           

          JOIN LOAD

               location,

               'Cost' AS type

          RESIDENT table1

          WHERE field = cost;

           

          JOIN LOAD

               location,

               'netrevenue' AS type

          RESIDENT table1

          WHERE field = netrevenue;

           

          Than your dimension in the chart bar is type and the expression is location. Use the unqualify above.

           

          I hope, it is helpful for you.

           

          Regards vicky

            • Re: fields as a chart's dimension

              hi vicky thanks for your reply,

               

              you can use the field name directly in the WHERE condition?

               

              in addition, i understand that we could selectively enable/disable expressions by using macros but can we do it for dimensions too? for e.g., wheni select Sales on the list box, only the Sales dimension will appear...

                • fields as a chart's dimension
                  Nagaian Krishnamoorthy

                  Did you consider using a different chart type such as scatter chart or bubble chart?

                  • Re: fields as a chart's dimension
                    Miguel Angel Baeyens de Arce

                    Hello,

                     

                    Assuming this dummy script

                     

                    CROSSTABLE (Item, Value) LOAD * INLINE [
                    Location, Sales, Net, Cost
                    ES, 100, 80, 20
                    NL, 200, 150, 50
                    UK, 150, 90, 60
                    FR, 120, 100, 20
                    ];
                    

                     

                    You can now create a new bar chart with Item and Location (in that order) as Dimensions and Sum(Value) as expression.

                     

                    Using Location and Item in two listboxes you can analyze, for example, only Cost of different locations.

                     

                    Is this what you are looking for?

                     

                    Miguel Angel Baeyens

                    BI Consultant

                    Comex Grupo Ibérica

                      • Re: fields as a chart's dimension

                        hi miguel thanks for your reply,

                         

                        how is a cross table different from what vicky has suggested above?

                         

                        i already have location, sales, net and cost as fields in table why do i need smth like your dummy script?

                          • Re: fields as a chart's dimension
                            Miguel Angel Baeyens de Arce

                            Hi,

                             

                            Depending on your data volume and data set, that will load faster, and is cleaner that joining several tables, since the information comes from the source with no need of joining or creating new fields. You can load the same data later on the script using different field names if needed.

                             

                            I suggest you to try and see which one fits best your needs and renders your chart as expected. If you already have it, feel free to ignore my suggestion. I've used that crosstable code in an example application getting the results as you have described.

                             

                            If you already have it, please mark vicky_qlik's post as answer so more users can get the solution.

                             

                            Regards.

                             

                            Miguel Angel Baeyens

                            BI Consultant

                            Comex Grupo Ibérica

                              • Re: fields as a chart's dimension

                                hi miguel,

                                 

                                i'm not really sure how i could apply it to my situation

                                 

                                for vicky's suggestion, it only has the location and string stating the type of field, where is it gonna get the value of sales/cost/netrevenue?

                                 

                                for your suggestion, what does the Item and Value in the brackets mean? how do i use it if i'm loading from a DB instead of specifying value explicitly?

                                  • fields as a chart's dimension

                                    Hi,

                                     

                                    yeah, why should I not use it in a where clause?

                                    The dimension in the chart is the "new" field called type. This field only includes the three words, so that you can read them above the axis and have the locations for each "word".

                                     

                                    Ok, you have to add a field to each LOAD, which includes the numbers.

                                     

                                    If you have the following example:

                                    Location, Sales, Net, Cost
                                    ES, 100, 80, 20
                                    NL, 200, 150, 50
                                    UK, 150, 90, 60
                                    FR, 120, 100, 20
                                    ];

                                     

                                    With the script above:

                                     

                                    Location, Number, Type

                                    ES, 100, 'Sales'

                                    NL, 200, 'Sales'

                                    UK, 150, 'Sales',

                                    FR, 120, 'Sales'

                                    ES, 80, 'Net'

                                    NL, 150, 'Net'

                                    UK, 90, 'Net',

                                    FR, 100, 'Net'

                                    ES, 20, 'Cost'

                                    NL, 50, 'Cost'

                                    UK, 60, 'Cost',

                                    FR, 20, 'Cost'

                                     

                                    I think, then your dimension in chart is type and location and the expression is Sum(Number). I can't try it out, I'm not at work.

                                     

                                    Regards

                                    vicky

                                      • Re: fields as a chart's dimension
                                        Miguel Angel Baeyens de Arce

                                        Hi,

                                         

                                        The code above is just an example on how to get a crosstable load working and the results it will return. I don't know your database, but it should work anyway tweaking the script a little.

                                         

                                        But for clarification sake, the crosstable above will return a table with three columns, one with Location, the other with Item (where possible values are "Sales", "Net" and "Cost") and the other one Value, storing all the amounts in the script, very similar to what Vicky describes above.

                                         

                                        Again, it's just an idea. If that doesn't fit your requirements or is more complex than the other solution, forget about it. I'll attach a very simple example application tomorrow morning for your perusal, just in case.

                                         

                                        Hope that helps.

                                         

                                        Miguel Angel Baeyens

                                        BI Consultant

                                        Comex Grupo Ibérica

                                        • fields as a chart's dimension

                                          i think i understand vicky suggestion for my initial objective

                                           

                                          what if now,

                                           

                                          • i need the dimension in weeks
                                          • for each week, there would be X number of stacked bars depending on the X number of locations i selected from a list box or another object
                                          • for each stacked bar, it will consist of sales/costs/netrevenue

                                           

                                          how could i modify the script further, or do i even need another script now?

                                           

                                          PS: i already have a date field that works properly in previous charts to show trends in weeks

                                          • Re: fields as a chart's dimension

                                            how do you use the WHERE condition with the field name?

                                             

                                            if i have a field name SALES im supposed to say WHERE field=SALES?

                                             

                                            i tried that and it tells me field not found,

                                            Field not found - <field>

                                            UTILIZATION_WEEKLY2:

                                            LOAD

                                                 Site,

                                                 START_DATETIME,

                                                 `90-000` as Value,

                                                 'PMFG' as Type,

                                                 TOTAL_TIME

                                            RESIDENT tUTILIZATION_WEEKLY

                                            WHERE field = `90-000`

                                             

                                            this is my original table,

                                            tUTILIZATION_WEEKLY:

                                            LOAD `START_DATETIME`,

                                                `10-000`,

                                                `20-000`,

                                                `30-000`,

                                                `40-000`,

                                                `50-000`,

                                                `60-000`,

                                                `70-000`,

                                                `80-000`,

                                                `90-000`,

                                                `A0-000`,

                                                `B0-000`,

                                                `C0-000`,

                                                `D0-000`,

                                                `E0-000`,

                                                `F0-000`,

                                                `TOTAL_TIME`,

                                                `PRODUCTIVE_MFG_LOSS`,

                                                'A' as Site;

                                            SQL SELECT *

                                            FROM A.`UTILIZATION_WEEKLY`;

                                             

                                            this is the script i used,

                                            UTILIZATION_WEEKLY2:

                                            LOAD

                                                 Site,

                                                 START_DATETIME,

                                                 `90-000` as Value,

                                                 'PMFG' as Type,

                                                 TOTAL_TIME

                                            RESIDENT tUTILIZATION_WEEKLY

                                            WHERE field = `90-000`;

                                             

                                            JOIN LOAD

                                                 Site,

                                                 START_DATETIME,

                                                 PRODUCTIVE_MFG_LOSS as Value,

                                                 'SITELOSS' as Type,

                                                 TOTAL_TIME

                                            RESIDENT tUTILIZATION_WEEKLY

                                            WHERE field = PRODUCTIVE_MFG_LOSS;

                                             

                                            JOIN LOAD

                                            etc...

                                              • fields as a chart's dimension
                                                Leonard Short

                                                In your load script you would do WHERE SALES = 'XXX'.

                                                 

                                                It sounds more like you are working on a chart though? In that case you would want to implement either some IF logic or Set Analysis should you need to limit the calculation to a subset of 'Sales'

                                                 

                                                In a chart you should just need to set your dimension to the same date field thats in your other chart & then add sum(sales), sum([net revenue]), etc... in a stacked bar chart.

                                                 

                                                Post a sample of your dataset & description of what you are trying to accomplish & we can likely clarify better what you need do.

                                                  • Re: fields as a chart's dimension

                                                    no im not trying to do a conditional load like that

                                                     

                                                    im modifying from what vicky has suggested above...

                                                     

                                                    for the chart it's not jus a single bar on each week but multiple bars depending on the number locations i've selected

                                                     

                                                    so if i selected 3 locations i have have 3 bars for each of the week and each bar has is stacked with the sales/costs/netrevenue

                                                      • fields as a chart's dimension

                                                        Good Morning,

                                                         

                                                        yes,

                                                         

                                                        Field not found - <field>

                                                         

                                                        is right, because you haven't got this name in your table. Are '90-000' ect. your fieldnames? Then you have write

                                                         

                                                        WHERE '90-000' = value (always WHERE field name = field value).

                                                         

                                                        Value is, what you see, if you load your data in a table.

                                                         

                                                        For example: field name = Sales, field values = 200,144,155,188,24,265

                                                         

                                                        And now is the condition: WHERE Sales > 150

                                                         

                                                        Then you load all data with 155,188,200,265

                                                         

                                                        Clear?

                                                         

                                                        Regards vicky

                                                          • fields as a chart's dimension

                                                            i understand how the mathematical operators work in a WHERE condition but now how you directly use the fieldname

                                                             

                                                            yes 90-000 etc are my fieldnames...

                                                             

                                                            values under x0-000 (where x is from 1-9 and A-F) are just integers which i will use to calculate stuffs

                                                             

                                                            WHERE field = '90-000' does not work...

                                                              • Re: fields as a chart's dimension
                                                                Miguel Angel Baeyens de Arce

                                                                Hi,

                                                                 

                                                                According to your script above, "90-000" is not the field name, but the value. The field name is "Value". So the script should look like

                                                                 

                                                                UTILIZATION_WEEKLY2:
                                                                LOAD
                                                                     Site,
                                                                     START_DATETIME,
                                                                     '90-000' as Value,
                                                                     'PMFG' as Type,
                                                                     TOTAL_TIME
                                                                RESIDENT tUTILIZATION_WEEKLY
                                                                WHERE Value = '90-000';
                                                                

                                                                 

                                                                Both values and field names are case sensitive, so "Value" and "value" can be different fields in the same table.

                                                                 

                                                                Hope that helps.

                                                                 

                                                                Miguel Angel Baeyens

                                                                BI Consultant

                                                                Comex Grupo Ibérica

                                                                  • fields as a chart's dimension

                                                                    oh i have to follow the "'90-000' as Value" i typed above?

                                                                     

                                                                    i thought the WHERE field = '90-000' was talking about the resident table instead of this new table...

                                                                      • Re: fields as a chart's dimension
                                                                        Miguel Angel Baeyens de Arce

                                                                        Hi,

                                                                         

                                                                        The script above loads from the resident table tUTILIZATION_WEEKLY those records where field "Value" are equal to "90-000". Anyway, it souns very confusing to me. According to your original script, the load should be as follows

                                                                         

                                                                        tUTILIZATION_WEEKLY:
                                                                        LOAD `START_DATETIME`, // all these are fieldnames coming from the A.UTILIZATION_PWEEKLY table in your database
                                                                            `10-000`,
                                                                            `20-000`,
                                                                            `30-000`,
                                                                            `40-000`,
                                                                            `50-000`,
                                                                            `60-000`,
                                                                            `70-000`,
                                                                            `80-000`,
                                                                            `90-000`,
                                                                            `A0-000`,
                                                                            `B0-000`,
                                                                            `C0-000`,
                                                                            `D0-000`,
                                                                            `E0-000`,
                                                                            `F0-000`,
                                                                            `TOTAL_TIME`,
                                                                            `PRODUCTIVE_MFG_LOSS`,
                                                                            'A' as Site;
                                                                        SQL SELECT *
                                                                        FROM A.`UTILIZATION_WEEKLY`;
                                                                        
                                                                        ResidentLoadTable1:
                                                                        LOAD Site, // this field will always have value "A"
                                                                             START_DATETIME, // this field exists in the resident table
                                                                             "90-000" AS Value, // this field exists in the resident table, 
                                                                                                // I always use "" or [] to mark field names in QlikView when they have special chars
                                                                             'PMFG' as Type, // this is a new field you are creating in this table
                                                                             TOTAL_TIME // this field exists in the resident table
                                                                        RESIDENT tUTILIZATION_WEEKLY; 
                                                                        

                                                                         

                                                                        You don't need the WHERE in this case. This table loads, based on the previous one, only the values in field "90-000" (now called "Value" in this new table) as "PMFG" as Type. You are already using this new "Type" field, so you don't need to specify which data you want to get from the resident load.

                                                                         

                                                                        The following one should be

                                                                         

                                                                        JOIN LOAD
                                                                             Site,
                                                                             START_DATETIME,
                                                                             PRODUCTIVE_MFG_LOSS as Value,
                                                                             'SITELOSS' as Type,
                                                                             TOTAL_TIME
                                                                        RESIDENT tUTILIZATION_WEEKLY;
                                                                        

                                                                         

                                                                        In this case, Value is filled with values from original field "PRODUCTIVE_MFG_LOSS", and stored and loaded into the "Value" field. Again, there's no need to use the WHERE clause.

                                                                         

                                                                        Hope that helps to understand how this works.

                                                                         

                                                                        Miguel Angel Baeyens

                                                                        BI Consultant

                                                                        Comex Grupo Ibérica

                                                                         

                                                                         

                                                                        P.S.: I'm still thinking of using the crosstable load here, now that I know your main table, because it makes a lot of sense to me, but nevermind.

                                                                          • Re: fields as a chart's dimension

                                                                            if i wanna have weeks as a dimension

                                                                             

                                                                            and on each week, there would be X number of bars depending on the X number of selections i make on the Site list box..

                                                                             

                                                                            each bar is a stacked bar with the various calculations

                                                                             

                                                                            so assuming my dimension has 6 weeks and i chose 5 sites from the Site list box

                                                                             

                                                                            i would have a total of 30 stacked bars, 5 for each week...

                                                                             

                                                                            will the script still work?

                                                                             

                                                                            am i supposed to set the week as the primary dimension and Site as the secondary dimension?

                                                                             

                                                                            on the axis, i would like to have the Site name and Week number appear below the axis

                                                                             

                                                                            for e.g.,

                                                                             

                                                                            BAR-A BAR-B BAR-C

                                                                            ------------------------------ this is the axis line

                                                                              SiteA   SiteB   SiteC   this is the sites

                                                                                             1                  this is the week