Filter chart AND include nulls

[gramqlik]

Hi,

I have 4 bar charts showing daily activity, all based on the same data, differentiated by Site.

Each chart has 2 bars per Activity - one for Today and one for Yesterday.

There are a number of possible activities (say, 20) but I only want 8 of these to be included on these charts. I've created a flag in the back end to identify these activities.

The frequency of the activities differs for each Site (i.e. each chart), so on a given day, one site may conduct all 8 flagged activities, but another may only conduct 3. This is a problem, because the bar charts will then display differently - null values are not shown by default and the bar sizes expand to fill more of the chart plot area, which looks messy and the charts become harder to compare at a glance.

- Under Expressions, I tried adding the Flag field to the expression for each date.

e.g.

COUNT({<SiteID={'ABC'},

[Date Stamp]={"$(=DATE(MAX([Date Stamp]), 'YYYY-MM-DD'))"},FlagA={1}

>}SiteCount)

This doesn't seem to make any difference. If the Show All Values checkbox is unticked, it displays only those activities that were conducted (today and/or yesterday). If the Show All Values checkbox is ticked, it displays all 20 activities (but obviously, only those where activity took place have corresponding bars on the chart).

- Under Dimensions, I tried replacing the Activity field with a Calculated Dimension using an IF statement to apply the Flag.

e.g.

=IF(FlagA = '1', Activity)

- I also tried hard-coding the desired activities with an IF statement:

e.g.

=IF(MATCH(Activity, 'A1', 'A2', 'A4', 'A7', 'A8', 'A12', 'A13', 'A15'), Activity)

In both instances, I see that using a Calculated Dimension greys out the Show All Values checkbox, so the chart then displays only the Activities that have values.

So..... what I need is for each chart to list the 8 flagged activities on the y axis, even when the count (x axis) for both of the 2 dates are null, but not show the other 12 activities that are not included in my flagged list.

I kind of need a Show All Values that works on a filter (Show Selected Values). It seems like this should be possible....

Thanks

G.

[gramqlik]

Just some added info... I've just noticed that with the Dimension set to =IF(FlagA = '1', Activity), I had a Flag Listbox set to 1 (i.e. included).

Upon clearing the listbox selection, an additional bar appears on the chart for each day, which I assume shows the Count of all the undesired activities (those not in the FlagA list).

Ticking the Suppress When Value Is Null checkbox hides these additional bars.

I've tried playing around with Activity field and the Dimension checkboxes, including adding an Activity listbox and making selections from there, but I still cannot get it to display the desired 8 Activity values.

[gramqlik · Accepted solution]

Ok, I finally found someone had a similar issue, and Sunny Talwar's solution (Re: Restrict dimension values but show all of them) worked for me too.

Following that example, I set the Calculated Dimension to:

IF([FlagA] ='1', Activity, Null())

With the Suppress When Value Is Null checkbox ticked.

I then included an additional Expression:

=Only({1}Activity)

and it successfully shows all 8 desired Activities, even when they're null, and does not show the unwanted 12 Activities.

So thanks to Sunny for his answer to someone else's post!

G.