As you can see I already used that kind of solution to fill all the NULL values with 0, but the issue appears when combinations for a whole column/row are missing. For example, in the 1st image, there are 0 records with Disposition Partial (in the image the column is filled with 0s because I created dummy data, i.e. create 5 more records with Disposition Partial and with all the Activity States, Delivery/Failed/etc.). But the solution with dummy data is ugly and I have a lot of filtering options, I would have to create like tens of thousands of dummy rows ... The issue is that, for this kind of columns, I don't event have NULLs ... the data just doesn't exist at all...
Apologies for missing that.
Just wanted to clarify: when you are referring to dummy data,
is that a scaffold on a separate sheet/datasource
that has every combination of State and Disposition?
That is, are there tens of thousands of combinations of State and Disposition?
Also, what is your type of datasource?
Attached in the Forum thread is a mocked up datasource.
Please adjust it to more closely match yours.
Also attached is workbook using the scaffold method.
Hello again, Swaroop,
My datasource is MSSQL/Oracle.
When I talked about dummy data I meant that I create temporary tables with all the Dispositions/States/Types/etc and from their data I create one big temporary table that contains all the available combinations of the fields that will be used for filtering (the fields that won't be used for filtering are all NULL). I use UNION ALL to add this data to my Views.
The issue is that I have a lot of filtering options (10) and some of them might have more than 10 possible options (Disposition only has 4 options and State 5, but when multiplying with 3 more options for filter A * 4 for filter B * 3 for filter C * 4 for filter D * about 15 for filter E and so on, I get a lot of dummy data/scaffold ). The client plans to use this Dashboard with tens of millions rows of data. I'm not sure about how this solution will affect the performance, as I am still new in my job of DB Dev/Data Analyst. Also, PM said that he doesn't likes this idea....
Sorry, couldn't make much headway.
I looked through the options in this post, which you've probably already gone through,
but it didn't seem like it was going to work with your particular filter scenario.
One other suboptimal attempt is attached here.
It unions just a table of the 20 combinations of Disposition x State
without any dimensions, except for a new column just to say that it is dummy data.
Simply, every filter will check if a row is dummy data and put that into a "Baseline" option,
otherwise just return the rest of the filter values. And so this will need to be a
multi-select filter with "Baseline" always checked, plus the user selected choices,
and then 0s will always show. Certainly suboptimal because of having to keep the "Baseline" option clicked.
Another method is using parameters, but suboptimal because those are not dynamic.
Hoping others in the community will have other ideas.
267867missingB.twbx 16.2 KB
One more hello, Swaroop,
This is a very nice idea. The only thing that don't let me to use it (in my Dashboard) is the fact that the only way (I found) to exclude a filter option from final user (but still keep its data on the sheet) is to use a parameter (as in this example Tableau - Filter has one value always selected - Stack Overflow ). But, the parameter only allows to select one value at a time, but I need user to be able to select multiple options, at the same time.
I really appreciate the ideas you shared!