Data Restriction on an object

[Former Member]

Hello there I am brand new to the forum and might be here for a while as I have just been placed on a BO project for work and have only done some training. I want to thank you in advance for any help received.

I am trying to count the number of rows returned by a field but only when a particular date field is less than current date. I have created a measure object for this. The where statement did not work out for me when I combined with other objects, so I guess I need to use a case statement in my select statement. Following an example I have come up with the following:

COUNT(CASE SALES.SALES_DATE WHEN < sysdate THEN SALES.PRODUCT_NO ELSE 0 END)

However the error I have received is Invalid Expression: missing Expression. What am I missing?

[Former Member]

On the Webi side, if then else replaces case expressions and CurrentDate() replaces sysdate:

=Count([Product No]) Where ([Sales Date]<CurrentDate())

If you're creating a Universe level object, use:

COUNT(CASE WHEN SALES.SALES_DATE < SYSDATE THEN SALES.PRODUCT_NO END)

If you have ELSE 0 at the end, you'd count the 0 and end up with a wrong answer.

Regards,

Mark

[Former Member]

Thanks Mark, I actually ended up figuring it out because it took so long for my post to be approved for showing. But your answer was perfect!

[Former Member]

I'm sorry but you're trying to Count at report or query side?

Regards,

Rogerio