Problem: measure for Month to Date returns 0

building semantic layer
1

Hello,

I need to calculate the measure value from the first of this month to
todays date… I read thro the forum topics for month to date and came up with this sql statement… but it doesnt seem to work…it gives zeros…

avg(case when (TRUCK_UTLIZATION_FACT.SHP_DT>= trunc(SYSDATE,'month') and TRUCK_UTLIZATION_FACT.SHP_DT< =SYSDATE) then TRUCK_UTLIZATION_FACT.INBOUND_TRUCK_UTILIZATION
 end )

pls help me…also i need to generate an object for the same month last year …

thanks in advance
newbee

[edited, changed topic title. Please, choose a more meaningful title for your topic other than “urgent help” next time. Thank you, Andreas]

newbee (BOB member since 2004-07-23)

2

I’ve tried:

Select avg(case when (SC.CALLDATETIME >= trunc(SYSDATE,'month') and SC.CALLDATETIME <=SYSDATE) 
then SC.CALLAMOUNT
end ) From Salescall SC

in Toad and it works like a dream… So your SQL seems to be in order (just removed the space between ‘<’ and ‘=’).

At least, I don’t have any zeroes. If the amount is the correct average: I didn’t test.

jobjoris :netherlands: (BOB member since 2004-06-30)

3

Hello jobjoris,

Thank you for the response but it doesnt seem to work

it still calculates zeros…

newbee :reallymad:

newbee (BOB member since 2004-07-23)

4

And what does

Select trunc(SYSDATE,'month'), TRUNC(SYSDATE)  from dual

return?

Maybe you should do a trunc around your last date (so it removes the time if it’s a date-time field).

jobjoris :netherlands: (BOB member since 2004-06-30)

5

Please try to use descriptive titles for your postings. It makes the searching more efficient. TIA

Chris Pohl :us: (BOB member since 2002-06-18)

6

Hello jobjoris,

thank you…
the output (sysdate,“month”) is 8/1/2004 and for trunc(sysdate) is 8/11/2004

the same statement worked for the YTD object (cal from 1st jan to tilll date)

i tried trunc for the last date …it didnt help either…

newbee

hello chris pohl,

sorry…i realised that after i posted my msg…

thanks for correcting that…
newbee

newbee (BOB member since 2004-07-23)

7

thanks for ur responses…

I got it fixed with this code…

avg(case when 	
to_char(TRUCK_UTLIZATION_FACT.SHP_DT,'MM-YYYY') >= to_char(SYSDATE,'MM-YYYY') 
and TRUCK_UTLIZATION_FACT.SHP_DT <=SYSDATE 
then 
TRUCK_UTLIZATION_FACT.INBOUND_TRUCK_UTILIZATION 
else 0
end)

…now Im left with generating the same object for previous year(from sysdate)…

can anyone help me…

newbee (BOB member since 2004-07-23)

8

forgot to mention…i tried add_months but it wouldnt work

it gives me missing right paranthesis error…I m sorry it is a pretty crude way to code …

avg(case when 	
to_char((add_months(TRUCK_UTLIZATION_FACT.SHP_DT,-12),'YYYY')] >= to_char(add_months(SYSDATE,-12),'YYYY') 
and (add_months(TRUCK_UTLIZATION_FACT.SHP_DT,-12) <=add_months(SYSDATE,-12) ]
then  TRUCK_UTLIZATION_FACT.OUBOUND_TRUCK_UTILIZATION end )

thanks in advance for ur help

newbee

newbee (BOB member since 2004-07-23)

9

correction to the previous code

avg(case when 	
to_char((add_months(TRUCK_UTLIZATION_FACT.SHP_DT,-12),'MM-YYYY')] >= to_char(add_months(SYSDATE,-12),'MM-YYYY') 
and (add_months(TRUCK_UTLIZATION_FACT.SHP_DT,-12) <=add_months(SYSDATE,-12) ]
then  TRUCK_UTLIZATION_FACT.OUBOUND_TRUCK_UTILIZATION end )

it is still not parsing

newbee (BOB member since 2004-07-23)

10

Try:

AVG
(
CASE
WHEN    
   to_char ( add_months (TRUCK_UTLIZATION_FACT.SHP_DT, -12), 'YYYY') 
   >= to_char ( add_months (SYSDATE, -12), 'YYYY') 
   AND
   add_months (TRUCK_UTLIZATION_FACT.SHP_DT, -12) 
   <= add_months (SYSDATE, -12)  
THEN
   TRUCK_UTLIZATION_FACT.OUBOUND_TRUCK_UTILIZATION 
END
)

Note:
Do not use ]

Andreas :de: (BOB member since 2002-06-20)

11

Remove the “]” and us “)”

Edit: Oops, Andreas was one step ahead of me…

jobjoris :netherlands: (BOB member since 2004-06-30)

12

thanks Andreas …
the code parses well but I get SQL sentence error (when i try to use the object on the report )-- not a single-group group function

newbee

newbee (BOB member since 2004-07-23)