APPLYSIMPLE PROBLEM

[robo100]

I am trying to convert a list of dates in to a metric.

For example I have the following
01/01/02 10.00
01/02/02 20.00
01/01/03 30.00
02/01/03 40.00

What I would like to see is the following
pre 2003 30.00
2003 onwards 70.00

I have created an attribute using applysimple but it only returns 2003 onwards 100.00

It is just adding everything together and not differentiating between the dates.

However, when I run the sql it creates in Oracle it returns the correct results.

Very greatful if anyone can help.

 

[robo100]

I forgot to add, the attribute does return the correct values in my development system but I just can't see any differences. Could it be somewhere in the project configuration that is making my live system only return 1 row.

Thanks again

 

[entaroadun]

ApplySimple("(case when #0 >= '01/01/2003' then '2003 onwards' else 'pre 2003' end)",[Date Field])

 

[robo100]

Unfortunately I am stil returning only one result.

 

[SteveGilles]

You may also want to check the date that Oracle is returning...
I've had to do some odd manipulations in ApplySimples with dates against Oracle before. it's picky about the date format itself, so i usually include 'TO_DATE' :

ApplySimple("(case when #0 >= TO_DATE('01/01/2003') then '2003 onwards' else 'pre 2003' end)",[Date Field])

 

[robo100]

That didn't make any difference either. The formula is ok, when I copy the sql the report generates into Oracle directly and run it I get the correct result. I think it must be something in the settings I am missing.

 

[entaroadun]

Paste the SQL you are using. Oracle should actually throwing an error with the SQL that I believe MSTR should be generating. Oracle shouldn't be able to GROUP BY a CASE statement.

Something must be missing here...

 

[robo100]

It was an ODBC problem, now sorted and working.

Many thanks to those who tried to help.

 

[iolaper]

have you tried consolidations ?