Missing dimensions when filtering chart table

[vimalthehero]

i have a data simlar to below table, while creating a straight table visualization and filter, we see if sum is zero, it misses to show it all dimensions. is it possible to show all dimension even its zero?

any suggestions are welcome.

input data

Months	Type	Cost
2023-01	car	1000
2023-02	car	1000
2023-03	car	1000
2023-04	bus	1000
2023-05	bus	1000
2023-06	bus	1000

Straight table expected output when type (car) selected/filtered
Months	Cost
2023-01	1000
2023-02	1000
2023-03	1000
2023-04
2023-05
2023-06

[rubenmarin · Accepted solution]

Hi,in QlikView we had the opton to show all values of a dimension but I don't see that option on QlikSense.

The workaround could be to create all relations between Months a Type and add the missing rows to the data table.

to create all different cobinations.

tmpCartesian:
LOAD FieldValue('Months', RecNo()) as Months
AutoGenerate FieldValueCount('Months');

Outer Join (tmpCartesian)
LOAD FieldValue('Type', RecNo()) as Months
AutoGenerate FieldValueCount('Type');

And to add to the table you can just add them, or create a composite key to just add the rows that doen't have a combination already loaded. to take the easy way, just:

Concatenate (DataTable)
LOAD 
    Months,
    Type,
    0 as Cost
Resident tmpCartesian;

DROP Table tmpCartesian;

Note that this will not affect the sums, but if you do an average the zeros will affect that average. To avoid that you can add "Null() as Cost" instead of 0.

[BrunPierre]

Try this.

=Sum(Cost) + Sum(0)

[rubenmarin]

That's also a good trick, but to avoid selections it would be: =Sum(Cost) + Sum({1}0)