2 Replies Latest reply: Feb 21, 2012 9:23 AM by cweberwbb RSS

    Variables as expressions with multiple dimensions

      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):

       

      ScoreNameExpression
      TopLvlScoreEffScore+CostScore+RiskScore
      EffScoreEffOverlandScore+EffCargocapScore+EffTowingScore
      CostScoreCostBaseScore+CostOptionsScore
      RiskScoreNHTSAScore+AvoidanceScore

      The score data is similar to the following:

       

      VehicleScoreNameScore
      1EffScore.6
      1CostScore.5
      1RiskScore.8
      2EffScore.4
      2CostScore.1
      2RiskScore.8
      3EffScore.4
      3CostScore.7
      3RiskScore.2
      4EffScore.9
      4CostScore.5
      4RiskScore.4
      5EffScore.6
      5CostScore.8
      5RiskScore.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

          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

           

           

          VehicleEffScoreCostScoreRiskScore
          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:

          ExpressionScoreName
          TopLvlScoreEffScore
          TopLvlScoreRiskScore

           

           

          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