Display all categories whether data is present or not

[nancysinghal]

Hi All,

I have the below requirement.

I want to display a chart with
categories as Status: with values: A,B,C,D
Measure: App Count

After that user has region dropdown, from where you can select a particular region.

But reqquirement is tpo show all categories in chart: A,B,C,D
whether data exists or not for all categories for the selected region.

Any help is appreciated.

Regards,
Nancy

[hespora]

create an outer join between a query returning all cateqories and your actual data query. use that to feed the chart.

[nancysinghal]

Thanks for the reply hespora :)

What you have suggested is right and working, but my problem is : We need a region dropdown, and on user selection of a particular region, that outer query concept doesn't work.

Only the categories present for that particular region show up in the chart, rest are not getting displayed. Requirement is to display all categories, whether data is there or not.

Regards,
Nancy

[hespora]

Fairly certain it *should* work.

Just a few questions:
- are you using a chart object or a visualization object?
- if visualization, are you filtering that directly, or via a data deck?
- your dropdown is a standard data drop down control, i suspect?

I'll build this around some dummy data and get back to the thread

[hespora]

I have to apologize: When building it, it immediately became obvious that my original answer was only half the story...

you still need the outer join, but before that, you need the unique categories and regions respectively, crossjoin those, and *then* outer join the original query. use coalesce() to transform null values into zeroes in case you also still want to do calculations with your measure. See screenshot attached.