3 of 3 people found this helpful
I'll try to show you using Superstore step by step
Step 1. Create a calc that will render a 0 when your Measure Value is NULL
Ex. ZN( SUM( [SALES] ) )
Step 2. Right click on your Date and choose "Show Missing Values"
Step 3. Drag your ZN calc onto the Text Mark
And that's it!
For more info, and how this relates in other situations, please refer to this video by Joe Mako
If this doesn't resolve your issue, please post an example workbook with more details.
if you make the fund date a discrete pill type (blue) and do a show missing values, then the missing dates should appear. Does this work for you? See attached.
fund_try1.twbx 14.5 KB
If i am not clear please let me know
2 of 2 people found this helpful
Ok this is a little trickier, but here is how I would do it.
The reason you calc doesn't work is because you are doing a standard Row Level calculation on data that doesn't exist in your Source DB....I.E. your DB is returning anything from the Query Tableau is sending it, thus your ELSE statement gets rendered...
The creation of the Dates is being in done in Tableau through Densification, therefore we need to the DATEPART in Tableau.
The way we can do this is by creating a Table Calc in Tableau, and then using a DATEPART around it. This works because Table Calcs are computed in Tableau after it gets the data back from your DB.
Step 1. Create a date field using a Table Calc that you can use in another formula. This is leveraging Tableau's "datasource" as opposed to the source DB
IF ISNULL(MIN([Order Date]))
THEN DATEADD('day', 1, PREVIOUS_VALUE(MIN([Order Date])))
ELSE MIN([Order Date])
Previous_Value is an Iterative Table Calculation that allows you to return the Value of the Measure/Dimension from the Previous Partition. This is happening on Tableau's side of things, and therefore is able to leverage Data that does not exist in the source DB.
Step 2. Create the DATEPART function on that calculated field
Step 3. Drag the dayOfWeek Calc onto the Filter shelf and Compute Using Table Down. Then Adjust the Slider to be only 2 - 5
Now your viz will exclude Weekends...even though no Data Exists
Hope that helps.
Thanks a lot Rody,
i think you spend some good time on this. this works like a gem.
Will this work for a continuous Chart?