I solve such a problem with different loading the data.
In that case:
'Sales' AS type
WHERE field = sales;
'Cost' AS type
WHERE field = cost;
'netrevenue' AS type
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.
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...
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?
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.
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?
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.
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.
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
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>
`90-000` as Value,
'PMFG' as Type,
WHERE field = `90-000`
this is my original table,
'A' as Site;
SQL SELECT *
this is the script i used,
`90-000` as Value,
'PMFG' as Type,
WHERE field = `90-000`;
PRODUCTIVE_MFG_LOSS as Value,
'SITELOSS' as Type,
WHERE field = PRODUCTIVE_MFG_LOSS;
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.
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
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
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.
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.
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.
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
BAR-A BAR-B BAR-C
------------------------------ this is the axis line
SiteA SiteB SiteC this is the sites
1 this is the week