Calculating sum at Report Level basing on Date field 1

[Shoot]

Hi,
I need to calculate the sum and count of an object at the report level while comparing the database date field value with the Report's Currentdate value.

I tried to calculate it using "if-then" statement and also by using "where"

eg:
=<Count of databasefield1> where <databasedatefield2>=CurrentDate())

But, When i tried to use the above syntax it throws an error &quot;Syntax Error (DMB0007).&quot;

Can anyone help me out with anyother options for calculating the count or fixing up the error.

Keep Smiling
Shoot

 

[Sridharan]

When you use use a Where Clause in Reporter Variable then you need to have a Constant on the other side to what you are equating. So the workaround will be to create a Flag Variable and give it a value 1 if it is Current Date. Then use that Flag variable in the formula saying Flag = 1 etc.. Having said that we can provide more help if you can let us know the data and what you are looking out of it. That will provide some light.

Sri

 

[arunmds]

Try using Sum (Decode (Datefield, currentdate,1,0,0)). I assume you are using Oracle database

 

[Sridharan]

He maybe but I'm afraid he is looking for Reporter Level Solution.

Sri

 

[Shoot]

Hi
Thanks a lot.. for the quick response.
I am presently working with &quot;Teradata&quot; database.
I actually wanted to compare two dates
i.e. I need the count of the persons in the previous month and also last year December month (These months may vary depending on the current year).
So I am trying to calculate these dates from the currentdate() and comparing the values with the database &quot;date&quot; field in order to get the person count.

eg: if(databasefield=previous month this year)then
get the total personcount for the month
else if(databasefield=Previous year December Month)
get the total person count for the month

The calculated data will be displayed in two different cells..
Rest all the dates will be ignored....

Hope this information will give you a clear idea of the calculation..

Keep Smiling
Shoot

 

[Sridharan]

Shoot,

I would suggest you to do this at the universe level rather at the report level. You need to use CASE WHEN ELSE END to compare the dates and then show the value for previous month and last year same month usng two objects.

CASE WHEN Databasefield = previous month THEN total person count END


CASE WHEN Databasefield = Previous year December Month THEN total personcount END

In Teradata you can use CAST with EXTRACT statement to do the manipulation or if you have a Calendar Table which I'm sure it will be your work becomes easy.

Sri