Count <measure1> ForEach <measure2> Problem.

Hello,

The actual problem is a lot more complex than what I am asking now; but on the first step to solve the problem; I am stuck. Could anyone suggest any idea?

Typical problem (as I have searched this form). I have a dimension called and then . A main order could have more than 1 sub orders. All I need to do is to count the number of for each .

I tried to use “ForEach” formula (as in the title above) but the result always show as 1.

I tried to break the and it still shows as 1.

Sample Data

<Main Order> <Sub Order>
A               A1
A               A2
B               B1

Any ideas why?

Thanks.


ardneh :indonesia: (BOB member since 2004-07-21)

Why aren’t you just doing this at the universe level?

At the report level, you need to have something different:

The formula for the count should be:

=Count(<Sub Order>) In <Main Order>

Steve Krandel :us: (BOB member since 2002-06-25)

Geez… That’s the answer? I see the function “Count <> IN <>” before in this forum; but I was thinking that it should not be the case. “ForEach” sounds more logical to me.

Why don’t I do this in the Universe? Because I don’t have access to this; just an end-user in my case; have no idea at all about the universe.

Thanks for your help.

Rgds,
Hendra


ardneh :indonesia: (BOB member since 2004-07-21)

Ardneh,

Did u try the CountAll function --counts all duplicates also,
CountAll ForEach
count will take only distinct values only thats why ur getin 1 only for Main.
try the above


mayur :india: (BOB member since 2004-07-22)

Hi,

I have tried the CountAll with ForEach but it does not work as well.
The solution by Steve Krandel by using Count with IN works perfectly.

Thks for your reply.

-hendra


ardneh :indonesia: (BOB member since 2004-07-21)