counting different variables

[Lhuffst]

I am trying to create a chart (working on data part right now) that counts the number of instances certain conditions exist. For example, if contract a has been advertised but not awarded then advertised should get a count of 1; if it has been advertised and awarded then awarded gets a count of 1 (not advertised).

I thought an if statement would work if I created a formula for each variable but I am having problems.

IF 
{Command.TASK_NAME} = "Execute Notice of Award (NOA)" 
AND 
(NOT iSnULL({Command.TASKACTSTART}) AND NOT ISNULL({Command.TASKACTEND})) then 1 else 0

In the above code, the return value should be a 1 because the task start and end dates have been filled in for that task_name. If I check on the dates alone, it works but when I add back in the task_name, it goes to 0. I did try adding trim in front of task_name but that didn't work either.

Ultimately, I need 4 columns Awarded, Contracts in Ad/Award, Advertisement in 2012 and Advertisement in 2013.

Also, since I need to make a bar graph, should I proceed with this theory or is there a better way to create the graph?
Thanks
Lhuffst

 

[IanWaterman]

Are you sure case of text is correct, try using upper case

IF
uppercase({Command.TASK_NAME}) = "EXECUTE NOTICE OF AWARD (NOA)"
AND
(NOT iSnULL({Command.TASKACTSTART}) AND NOT ISNULL({Command.TASKACTEND})) then 1 else 0

Ian

 

[Lhuffst]

Ian, I tried that but it didn't work. The entire field is 480 characters long so I'm going to try the trim on the sql side. I am wondering if I need to do something with groups.

 

[Lhuffst]

Is it a problem because I mixed text and dates?

 

[IanWaterman]

In the condition clause you can mix data types. You can not mix data types in result, your results are both numbers so all is OK.

Ian

 

[Lhuffst]

OK THANKS. I just checked with the DBA's (again) and they are now saying people actually type the answer in for task name. Based on this, I changed it to like '*(NOA)' and now it's counting correctly. There must have been an extra space that I didn't see. Thanks for the help.