Cognos 8.4 filling in zero in empty fields

[Sidney786]

Hi,

Does anyone who I could introduce zero when no data in the bucket range.
CASE
WHEN [Age] <= 0 THEN 'Future Value'
WHEN [Age] between 1 and 30 THEN '1 - 30'
WHEN [Age] between 31 and 90 THEN '31 - 90'
WHEN [Age] between 91 and 180 THEN '91 - 180'
WHEN [Age] between 181 and 360 THEN '181 - 360'
WHEN [Age] > 360 THEN '> 360'
END
thx
Sid

 

[drlex]

Do you want the lack of data (NULL) in Age to show as a zero?
If so, could you add WHEN [Age] is MISSING THEN '0' to the conditional?

soi là, soi carré

 

[blom0344]

In C8/C10 you can format your data including missing values as '0'

Ties Blom

 

[Sidney786]

Thx guys
I have tried the data format option it didn't work and tried forcing it to zero by placing else '0' at the end neither worked.

 

[Sidney786]

It has been suggested that I create a two part query 1 to calculate the buckets 2nd to populate them. Any ideas how i would do that?

 

[blom0344]

Did you try and define an ELSE part in your CASE statement? If no condition is met in the THEN part of the CASE the ELSE should handle it.

Ties Blom