topic fields as a chart's dimension in QlikView

fields as a chart's dimension

Thu, 23 Jun 2011 09:34:21 GMT

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

Fri, 24 Jun 2011 07:56:12 GMT

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

Tue, 28 Jun 2011 09:42:53 GMT

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

nagaiank — Tue, 28 Jun 2011 09:59:47 GMT

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 — Tue, 28 Jun 2011 10:05:48 GMT

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

Tue, 28 Jun 2011 12:22:10 GMT

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

Tue, 28 Jun 2011 12:23:23 GMT

hi kris thanks for your reply,

i didnt consider using the 2 charts u've mentioned because i dun think they would be appealing to who will eventually be looking at it

Re: fields as a chart's dimension

Miguel_Angel_Baeyens — Tue, 28 Jun 2011 12:28:51 GMT

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

Tue, 28 Jun 2011 15:58:36 GMT

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

Tue, 28 Jun 2011 17:59:56 GMT

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 — Tue, 28 Jun 2011 21:17:31 GMT

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

Wed, 29 Jun 2011 01:46:14 GMT

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

Wed, 29 Jun 2011 02:47:08 GMT

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

Wed, 29 Jun 2011 03:10:02 GMT

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

Wed, 29 Jun 2011 03:36:31 GMT

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

Wed, 29 Jun 2011 05:12:10 GMT

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

Wed, 29 Jun 2011 07:39:00 GMT

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 — Wed, 29 Jun 2011 07:45:50 GMT

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

Wed, 29 Jun 2011 08:36:43 GMT

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 — Wed, 29 Jun 2011 08:51:10 GMT

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.