how to use set analysis to display a derived metric

[daveatkins]

I have a complicated qlikview dashboard with many table charts. The data model uses a fact table with values stored such as

metric, budgetvalue, actualvalue--and numerator/denominator for each due to the fact that some metrics must be calculated as ratios and survive proper filtering.

metric are grouped in categories, so a given table on the page might have it's dimension defined as all the metrics from one category, then the columns are sum(budgetvalue), sum(actualvalue)

now I need to display a new metric which is the product of dividing two existing metrics; e.g.

metric a / metric b

the new metric c already exists in the data model but the current values are inaccurate/need to be overridden by the calculation below.

I defined a variable vMetricC :

(

Sum({<Metric={'MetricA'},MonthYearID={$(=max(MonthYearID))}>}(FactValueNumerator))

  /

if(Sum({<Metric={'MetricA'},MonthYearID={$(=max(MonthYearID))}>}(FactValueDenominator))=0,1,Sum({<Metric={'MetricA'},MonthYearID={$(=max(MonthYearID))}>}(FactValueDenominator)))

)

/

(

Sum({<Metric={'MetricB'},MonthYearID={$(=max(MonthYearID))}>}(FactValueNumerator))

  /

if(Sum({<Metric={'MetricB'},MonthYearID={$(=max(MonthYearID))}>}(FactValueDenominator))=0,1,Sum({<Metric={'MetricB'},MonthYearID={$(=max(MonthYearID))}>}(FactValueDenominator)))

)

The expression $(vMetricC) works in a text box, but when I try to include it a table of metrics, it will not display that row:

if(Metric='MetricC',Num($(vMetricC),'#.##') )

I kind of understand why this does not work, but I don't know how to make it work. Can anyone help?

[swuehl · Accepted solution]

Try adding the TOTAL qualifier to all your aggregation functions.

If you are using Metric as dimension, a MetricA / MetricB value will not be grouped in a MetricC dimension otherwise.

[daveatkins]

Thank you!