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?
