filtering calculated element?

#1

Hi there! I’m trying to filter based on a calculated element on a dashboard and can’t figure out why it’s not working!

I am looking for a case closure date that is null, and was struggling creating a date filter that only selected “null” results, so I created a calculated element that is a column called “Case closed?”

When date = null, it returns false
when date = anything else, it returns true

I wanted to filter my results for the user so that they would only see a report of people with “false”, ie the case is still open.

When I create a filter based on this column, and select “false” results, it returns nothing.

Do filters not work on calculated elements for some reason? Does anyone have a good way around this if so, or an idea for handling this differently?

Thanks

#2

I would calculate the true/false condition in the data cube, not at the dashboard, then filter that result. And then you could use the language you’d like to see the result (case open, case closed) and status color…

#3

sorry if that was not clear, I did calculate it in the cube! I’m not sure why the filter is not working.

#4

Morning @heather,

Are the filters ‘hooking up’ to the new field that you created with the DataCube? Depending on the type of filter that you use and the data type you are outputting for the column, you might not be able to check off your calculated column. As an example, you can’t hook up a boolean field to a ‘value filter’. You’d have to use a ‘checkbox filter’ instead.

1 Like
#5

While you’re evaluating @jeff 's suggestion, I would step back and confirm the calculated element is displaying correctly in the data cube. I know it’s slow navigating through the data preview, but I like to confirm the criteria and results before moving on to the filters and status treatment. Perhaps share your calculated element?

1 Like
#6

I think my very simple calculated element is returning correctly, and did check through the data preview, but I’m definitely open to suggestions:

if ($CaseClosureDocDate$ == null)
{
return “false”;}

else {
return “true”;
}

…

I am just looking to see quickly whether there is a case closure doc date or not, and filter based on that, so that some of the people using this data can quickly see if the client is still open, and then planned to try to filter based on this so that most of the time they would not be looking at closed clients except for research (ie, if we were concerned something was closed accidentally, etc.)

…

I think that what Jeff says makes sense and I might not have been using the right filter before, as soon as I read his statement I realized I didn’t actually know the types of data different filters were limited to (except the very obvious, like date time) however I went to use the check box list filter and had the same result. As soon as I select a checkbox in view mode my table returns no results.

When I display it in the dasboard with row headers, it seems very clear that the true / false statements are working, I am just not clear on why no results would return once it is filtered.

Is there something additional I have to set up when using boolean data values in a column of a table? I’m not displaying it as a flat column, not sure if for some reason that would impact the interaction?

I’m scratching my head and playing around with filters and trying to think of other reasons this might actually be, too, now.

#7

Hi @heather,

Since in your code you’re return “true” and “false”, the data type is going to be a string meaning you need to use a Value Filter. If you remove the quotes, you’ll get a boolean for the check box filter.

This should be fairly straightforward so something else might be at play. Maybe try to re-build the visualization on a fresh dashboard and see what happens? It certainly shouldn’t go blank unless there is some odd double filtering or something going on.

1 Like
#8

thanks, yea it does seem a little odd - this all makes sense, I’ll try it

#9

uuugh… I actually did just need to change it to boolean. In the data cube I had made the type boolean, but returned those string values, and I think that when I used those filters no results were coming up maybe because it basically wasn’t really a string or boolean value… ahh thanks for helping me slog through this, can’t believe it ended up being that simple. Anyway, I have a lot to learn and won’t make that mistake again.

2 Likes
#10

great to hear Heather!

1 Like