Need Case Statement to Return a Zero if Null

[wwconslt]

Hello Everyone,

Using Relational CQM Model.

I created the following case statement.

It works except for one problem. I'm using the results to group on. Sort of a row heading. The user wants to see the row heading even if there are no results ($0) for that line's criteria. It works except when there are no totals that fall in that range. For example, $10M-$25M. Since there are no totals in that range, it doesn't appear in the list. I would like a $0 to appear. I also need to use that $0 for other calculations in other columns, so setting the data format property to show 0 when null won't do the trick.

Should I use nested IF statements?

If I do use a table with singletons to show zeros, is there a way to reference singletons in a layout calculation like Singleton1-Singleton2?

Any suggestions greatly appreciated!


===============

CASE

WHEN ([Total] < 100000)
THEN ('Under $100K')
WHEN ([Total] between 100000 and 249999.99)
THEN ('$100K-$250K')
WHEN ([Total] between 250000 and 499999.99)
THEN ('$250K-$500K')
WHEN ([Total] between 500000 and 999999.99)
THEN ('$500K-$1M')
WHEN ([Total] between 1000000 and 2499999.99)
THEN ('$1M-$2.5M')
WHEN ([Total] between 2500000 and 4999999.99)
THEN ('$2.5M-$5M')
WHEN ([Total] between 5000000 and 9999999.99)
THEN ('$5M-$10M')
WHEN ([Total] between 10000000 and 24999999.99)
THEN ('$10M-$25M')
WHEN ([Total] > 25000000 )
THEN ('Over $25M')

ELSE ('error')
END

=============

[dougp]

If I understand the question correctly, you want all of those categories to appear in your output, even if there are none actually in the data.

You'll need to create a query that produces all 10 of those values in a column, then left outer join that to the query that is currently producing the output for your report.  You'll then use the column from the new query in the output.
You may need to use coalesce() to get zeros to appear.

[wwconslt]

Thank you! I'll give that a try.

[wwconslt]

Hi Doug,

What's the best way to "....create a query that produces all 10 of those values in a column" without using any actual dollar values to populate? I just want all 10 categories in that one query to do the join on.

Total Level

Under 100K
100K-250K

etc.


Thanks

[dougp]

Can't paste SQL code:

We are temporarily experiencing a problem with this page or site. Thank you for your patience as we work to resolve this issue. Please check back later.

It's a UNION query.

select 'value' as val
union select 'value2'
...