I’m creating measures in a universe. I’m using @select(formula). I created 2 of these. I plan to divide them into each other to get a percentage. Would I need to put @select with it? Below is what I created:
="@select([Count (Returned)]/[Count (Sent)])"
But that doesn’t give any results. Just displays the formula. But when I use.
=[Count (Returned)]/[Count (Sent)]
I get the percentage I’m seeking (in a report that I’m testing).
So, if I want to create a measure in the universe that calls other measures can I just use the example above?
All measures where a measure is divided by another measure should be defined on a report level. Unless you are using BO XI 3 and you will be using database delegated measures.
[Moderator Note: Moving from WebIntelligence XI forum to Semantic Layer / Universe Designer]
Anything in quotes is going to display as text. If you want to reference other objects, each has to be referenced individually. The proper format would be:
You probably have a fan or chasm trap. Either is indicated by inflated measure values. There are tons of topics on the board (and blog posts on the web) related to fan or chasm trap resolutions.
Creating the alias worked. But, when I create a measure to give me the percentage when the returned count is divided by sent count I still get 1. So, basically it’s still having the same issue. But, if I just bring in Return Counts and Sent Counts those numbers are right. It’s just when I create the measure to get a percentage it doesn’t work.
At this point you’ve described your symptoms but have not provided enough specifics about the structure behind the problem. What does your universe look like? What is the SQL generated for the two measures together? For the measures used individually?
When I pull those two measures into a report, they work perfect.
But, when I pull the percentage measure (below) in, I get a percentage of 1. That’s because it’s have the initital issure of assigning both measures a count of 444.
WHERE
( TRANSFER_ORBIT.tid=Eval_Svc_Reports.tid )
AND ( TRANSFER_ORBIT.tid=Eval_Svc_Reports_Alias_Dont_Use.tid )
There’s your problem. The number returns 1 because you’re ending up with the same condition going in two different directions. The only possible result is that the value from Eval_Svc_Reports will be identical to the value from Eval_Svc_Reports_Alias_Dont_Use because of the joins.
When you do joins, all of the joins have to be true at the same time. So in this case, the “tid” is the same from the TRANSFER_ORBIT table as well as the other two tables… all at the same time. You mentioned the number 444 earlier. What you’re probably getting is 444 / 444.
Most percentages and averages don’t belong in the universe, they should be done on the report. This is one of the reasons.
There still feels like there is something missing. What is supposed to be different about the two counts? In the SQL you posted, there is nothing to distinguish one from the other. But in your earlier posts you said you were getting different numbers.
(BOB member since 2003-11-27)
(BOB member since 2002-06-06)
(BOB member since 2002-06-20)
The number returns 1 because you’re ending up with the same condition going in two different directions. The only possible result is that the value from Eval_Svc_Reports will be identical to the value from Eval_Svc_Reports_Alias_Dont_Use because of the joins.