Show the data in the Cross table even if there is no data on the selected date in Spotfire

[Diana Arias]

Hello! I created a vehicle accident case to ask this question.

I have a Date filter to filter the count of vehicle accidents. I'll show what I need using the Renault as an example:

In this case, I'm filtering the date: 11/08/2022, as you can see, the Renault appears because we have accidents:

 

But in this case, if I filter the 11/07/2021 the Renault does not have accidents, so for that reason, the Renault does not appear:

 

What I need: Even though I select a date where a vehicle did not have an accident, I need to see all types of vehicles. In this example, I need to see the Renault in the type name list (this can be with a 0 or "-"):

 

[Fredrik Rosell]

Hello,

As you want to see values that don't exist in your filtered data, one approach could be to not actually filter your data, but to use the date of interest directly in the calculations for the plot instead.

In the example below, I created a Document Property (propDate) which contains the Dates from my Date column. At the top of the page, I added a drop down where you can select the Date you are interested in.

I use that property in the expression for the cell values in the Cross table:

Integer(Sum(If([Date]=DocumentProperty("propDate"),1,0)))

This expression just sums the values from all rows - 1 if the Date matches what is set in the document property, 0 otherwise)

Below, I have selected the Date 10/2/2022, and you can then see that it counts the 3 Volvo's for that date, and Renault has 0..

 

Another approach in a case like this can be to add "zero data" data. Here's an example of a script that does that:

IronPython script to Add Rows With Zero

https://community.spotfire.com/s/article/IronPython-script-to-Add-Rows-With-Zero