Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

mjt_childrens
New Contributor III

Set Analysis Modier Question

Hi All....

I'm working on a Scorecard application. There are two tables: Metrics and CRMTransactions. They are linked by MetricId.

I have a straight table with Metric ID as a dimension. For one of my calculations I need to count transactions for a metric and divide them by the count of a different metric. It seems like this should be an easy calculation with set analysis but I can't seem to get it right. Here's my expression:

If(MetricId=5,count({$<CRMTranYear={$(=varYear)}>} CRMRecSource / count({$<MetricId={2}>}CRMRecSource))

I have tested the numerator portion of this equation and it returns the expected value. The denominator portion only returns zero when I test it by itself.

For Metric 5 how do count all of Metric 2 records to use as the denominator of my equation?

Thanks in advance, this has me stumped.

Martha

Tags (1)
1 Solution

Accepted Solutions
mike_garcia
Contributor III

Set Analysis Modier Question

Hello Martha,

Try this (just add the TOTAL qualifier):

If(MetricId=5,count({$<CRMTranYear={$(=varYear)}>} CRMRecSource / count({$<MetricId={2}>} TOTAL CRMRecSource))

Mike.

2 Replies
mike_garcia
Contributor III

Set Analysis Modier Question

Hello Martha,

Try this (just add the TOTAL qualifier):

If(MetricId=5,count({$<CRMTranYear={$(=varYear)}>} CRMRecSource / count({$<MetricId={2}>} TOTAL CRMRecSource))

Mike.

mjt_childrens
New Contributor III

Set Analysis Modier Question

Mike, Thanks. That was the key. I really appreaciate your help.

Community Browser