1 Reply Latest reply: Sep 30, 2011 5:33 AM by Stefan Wühl RSS

    How to use dimensions in the right hand side of set modifiers in set expressions?

    Jose Diaz



      We have a data model that contains a table with a log of activity dates, the id of the activity and the id of the individual that performed the activity (performers).  Activities are categorized by specialization, and another table in the data model contains KPI objectives for the number of activities per specialization, month and year.  These tables are connected through a path that includes another table which contains several attributes of the performer of the activity - including the specialization they have.  So, basically an activity is carried out by a performer that has a specialization and the values for KPIs related to a specialization are loaded into a specializations table.   


      So, we need to get a table chart where we show several expressions, including the number of events per month for a given specialization as well as the objective of activities per month for that specialization.  We use as dimension the month of execution for the activity - call it 'MonthActivity'.  To get the value of the objective, we are trying to use a set expression that just calculates the value of the objective for the month that is equal to the current value of the month field that we are using as dimension for the chart (the user selects the speciality through the QV UI).  As you have probably figured out buy now, this is not working.  We think that the cause might be that set expressions can not take as parameters of set modifiers the current value of the dimension used in the chart, e.g., avg({$<MonthObjective=MonthActivity>} QttyActivitiesObjective) where MonthActivity is the dimension used in the chart - not a field value selected through the QV UI.


      This must be something that many people might need to do in other applications, but it doesn't seem to be easy to achieve using set analysis.  What are we missing? Any ideas on how to do this?





        • How to use dimensions in the right hand side of set modifiers in set expressions?
          Stefan Wühl



          there are some things which are pretty hard if not impossible to be done with set analysis, you just ecountered one of those. Since the set analysis expression seems to be evaluated once for the whole chart, a comparison with the current dimension value within that expression will not work.


          To achieve somekind of comparison on dimension level, you could use an if() statement instead, maybe like

          = avg( if(MonthObjective = MonthActivity, QttyActivitiesObjective))


          Hope this helps,