Aggregate_Aware() with 'WHERE' clause to define a condition

building semantic layer
1

Hi,

In the object definition, can I use the ‘WHERE’ clause to define a condition with Aggregate_Aware() function in the ‘select’ clause.
If the answer is YES, how can I implement for an object definition in the universe? And how the condition will be applied on the Aggregated value selected?
If the answer is NO, how to achieve this functionality in the universe for an object definition?

Please suggest the possible approaches.

Thank You
JVNK

jvnk2k4 :india: (BOB member since 2007-12-05)

2

Try using a case statement to build the conditional logic without having to resort to using the where clause.

Nick Daniels :uk: (BOB member since 2002-08-15)

3

Hi Nick,

Thanks for your reply.

I am comparing the sales information with current_week and previous week.

current_week=Aggregate_Aware(T1.current_week,T2.current_week,T3.current_week)
previous_week=Aggregate_Aware(T1.previous_week,T2.previous_week,T3.previous_week)

So, In the 1st week of new year:
current_week=1
previous_week=52

When I run a query, the previous_week is taking as 52 and it is calculating for the new year’s 52 week which contains no information in the summary tables.

Now my exact requirement is:
Whenever ‘previous_week’ object is added to the report and if the current_week=1, then ‘previous_week’ should come as previous_week=52 and year=previous_year.

for this, I need to add a ‘WHERE’ clause condition as year=previous_year and ‘SELECT’ clause as Aggregate_Aware(T1.previous_week,T2.previous_week,T3.previous_week)

Please let me know if you need any clarification.

Thank You
JVNK

jvnk2k4 :india: (BOB member since 2007-12-05)

4

Hi,

Can anyone help me to achieve the functionality of using WHERE clause with Aggregate_Aware() function in the SELECT clause.

Thank You
JVNK

jvnk2k4 :india: (BOB member since 2007-12-05)