Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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?

21 Replies
Miguel_Angel_Baeyens

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

Not applicable
Author

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

Not applicable
Author

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...

Not applicable
Author

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.

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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...

Miguel_Angel_Baeyens

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

Not applicable
Author

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...

Miguel_Angel_Baeyens

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.