Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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...
Did you consider using a different chart type such as scatter chart or bubble chart?
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?
BI Consultant
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?
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
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.
BI Consultant
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?
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