How to count fact table records by comparing to numeric values in the

[renu123]

I need a count of records from my fact table based on a condition.

I create param_val fact from my fact table F1.
One of my dimensions is A1 (based on A1 table having columns Low and High)
For each record of the fact table, this param_val needs to be compared to Low and High.
I need to count all records which have Low < param_val <High

For this, I have created facts Low (From the dimension table A1) and High(from the dimension table A1)
Metrics LowM1 and HighM1 have been created as min(Low) and min(High)
(Here, also, is there any other way where I can get the absolute value of the column for a particular record. This use of min gets very messy on changing levels of attributes displayed on the report.)

I am creating a filter with set qualification for the mentioned condition.

Somehow, this doesn't work.
Please let me know how to go about ?

 

[nlim]

2 quick questions:
- Is the sql you want something like this?

select count(F1.param_value), abc
from F1, A1
where F1.param_value>A1.low
and F1.param_value<A1.high
and F1.xyz = A1.xyz
group by abc

if so, what is xyz and what is the abc you grouping by?

there's an ABS function you use instead of max or min.

 

[renu123]

Yes, the sql would look like this. But, i don't want abc to be displayed on the report.
I would use this count to arrive at yield and would want to display it week vs parameter (which are other two dimensions linked to the fact table)
Also, that ABS function gives problems.

 

[nlim]

here are some quick ideas for you to try since I don't have all the info clearly:

1) yield sounds like it is a ratio, and this count is in the denominator. this is fairly straightforward by using compound metrics
2) since abc is on report, and not used in your yield report with parameter and week, then you should not group by abc at all. I would suggest creating a metric with a filter in it.
3) you might have to use dimensionality of the metric to force the sql to run at the f table level.

this is a hard problem, i'm not sure but you might want to check with one of their consultants or tech support...