topic Variables as expressions with multiple dimensions in QlikView

Variables as expressions with multiple dimensions

Thu, 16 Feb 2012 14:21:46 GMT

I am currently working on a project on which I have created equations in Excel that are loaded in the QV script as variable and used in expressions for building charts. The equations are similar to the following (there are additional equation decompositions but I wanted to keep the list short here):

ScoreName	Expression
TopLvlScore	EffScore+CostScore+RiskScore
EffScore	EffOverlandScore+EffCargocapScore+EffTowingScore
CostScore	CostBaseScore+CostOptionsScore
RiskScore	NHTSAScore+AvoidanceScore

The score data is similar to the following:

Vehicle	ScoreName	Score
1	EffScore	.6
1	CostScore	.5
1	RiskScore	.8
2	EffScore	.4
2	CostScore	.1
2	RiskScore	.8
3	EffScore	.4
3	CostScore	.7
3	RiskScore	.2
4	EffScore	.9
4	CostScore	.5
4	RiskScore	.4
5	EffScore	.6
5	CostScore	.8
5	RiskScore	.5

What I want to do is enter the variable "TopLvlScore" from above as the sole expression in a chart and use "Vehicle" as the dimension. With the example data, the chart (bar) would need to show the vehicle ID along the x-axis, with the "TopLvlScore" on the y-axis. Vehicle 1 would be 1.9, Vehicle 2 would be 1.3, etc. How can I accomplish this? I appreciate any assist you can provide.

Re: Variables as expressions with multiple dimensions

Sun, 19 Feb 2012 07:58:00 GMT

Hi cweberweb

This is certainly possible.

Evaluating a dynamic expression

This is possible, as you can dynamically evaluate fields as expressions by using the $ sign expansion. EG $(xField) where the text in xField is '=field1 + field2' will evaluate to field1 + field2, instead of the text

There are several ways around this.. and it will depend on the size and structure of your data. Perhaps if you could post an idea of your data model I might be able to help more? Anyway, take your pick:

These first two assume that you have created the expression as a separate table (NOT joined to the other table). Hence when you select a ScoreName, just one expression is selected.

1)Change the expressions to pick out the values using set analysis

Change the expressions to read something like

'=sum({<ScoreName={"EffScore","CostScore","RiskScore"....}>} Score)'

Where the dimension is Vehicle, this will only sum the various scores needed in each score that you have selected.

2) pivot the score data in the load script

This will make the Expressions a lot simpler to write and execute, but might be ineffective for large data volumes or for lots of different types of ScoreNames.

Your ScoreData table would now look like

Vehicle	EffScore	CostScore	RiskScore
1	.6	.5	.8
2	.4	.1	.8

If [EffScore],[CostScore] and [RiskScore] are now fields, the expression for TopLvlScore can now just remain '=EffScore+CostScore+RiskScore'

If the scores are as consistent as they appear, you could join the expression table to the Score Data table using the ScoreName:

3) Add ScoreName to the Expression table

This would join the expression table to the ScoreTable. The idea is that the expression table now has many rows for each expression, and when you select an expression it will select the subsequent scores, and only sum them. This does not require any dollar sign expansions:

Expression table:

Expression	ScoreName
TopLvlScore	EffScore
TopLvlScore	RiskScore

When you select TopLvlScore, it will dynamically select just the Scores with EffScore and RiskScore in the ScoreName column of the other chart and so the expression in the chart will just need to be a sum of the Scores

Have I understood this correctly?!

Regards,

Erica

Re: Variables as expressions with multiple dimensions

Tue, 21 Feb 2012 14:23:01 GMT

Thanks for the reply Erica, I have attached a basic data model as you requested so you (and others) can take a look. Basically, we will only give scores to the very lowest level and the upper-level scores will be determined by the expressions in sheet1. This is a simplified version of the raw data as it will go down several additional levels in some of the categories. I appreciate any and all assistance!

Thanks!