9 Replies Latest reply on Jan 14, 2016 9:05 AM by Joe Mako Branched to a new discussion.

    Unable to Show Missing Values (Dates) when date level is switchable using parameter

    Tomek Zbrozek

      Hello,

       

      I am drawing a simple line chart on continuous axis (unfortunately, can't upload it). X-axis is a date axis (switchable, see below) and Y-axis is a measure value.

      My date field is a calculated field dependent on a parameter:

       

      CASE [Choose date granularity]

      WHEN "Day" THEN DATETRUNC('day', [Created At])

      WHEN "Week" THEN DATETRUNC('week', [Created At])

      WHEN "Month" THEN DATETRUNC('month', [Created At])

      WHEN "Quarter" THEN DATETRUNC('quarter', [Created At])

      WHEN "Year" THEN DATETRUNC('year', [Created At])

      END

       

      I would like to see the chart line following X=0 for each missing value, but when my date field is set as "Exact Value, continuous", it simply does not display the "Show Missing Value" button (screenshot below).

       

      Does anyone have an idea why this happens and what kind of workaround should I use to be able to Show Missing Value and maintain switchable date level (please note that I need to show missing values also when the week, month etc are missing, after switching to the higher date level)?

       

      Thanks!

      Tomek

       

       

       

        • 1. Re: Unable to Show Missing Values (Dates) when date level is switchable using parameter
          Carl Slifer

          Howdy Tomek,

           

          Let's assume this is for a measures called sales.

           

          If you need 0 every time sales is 0 for that period try the following.

           

          ZN(LOOKUP(SUM(Sales),0))

           

          What this does is it attempts to lookup every possible value. When there is no value lookup returns null. If Lookup returns null then ZN() returns 0 or it returns the lookup function, which happens to be the exact same as SUM(Sales) within that aggregated period.

           

          Cheers

          Carl Slifer

          InterWorks

          • 2. Re: Unable to Show Missing Values (Dates) when date level is switchable using parameter
            Tomek Zbrozek

            Hello Carl,

             

            Thanks for replying! Unfortunately, this solution doesn't help at all

             

            I am attaching a workbook recreating this problem (sorry, I should have done this before so that it's clear what exactly I am aiming to do).

            I am counting user_ids in this workbook, and date is switchable between days/weeks. Every 1st and 2nd day of each month is missing so the line should go down to 0 on these days (which happens when using either Exact date or Day setting, but does not, when I select date granularity from parameter and display using calculated field.

             

            Best,

            Tomek

            • 3. Re: Unable to Show Missing Values (Dates) when date level is switchable using parameter
              Tomek Zbrozek

              Another interesting fact:

              this syntax returns proper view (screenshot 1): DATETRUNC('day', [Date]), but when I set up a parameter Select date level containing 'day' and 'week' values, select 'day' and change the datetrunc calculation to DATETRUNC([Select date level], [Date]), the chart brokes down (screenshot 2). I think happens because of the fact that I can't turn on "Show Missing Values" when using parameter (but have no idea why this happens).

               

              Screenshot 1:

               

               

              Screenshot 2:

              • 4. Re: Unable to Show Missing Values (Dates) when date level is switchable using parameter
                Carl Slifer

                Howdy Tomek,

                 

                I cannot answer the the full question right now but the last one you

                postulated I can. When you use datetrunc you convert date to a datetime

                data type. Do you see the little clock next to the calendar on be

                dimensions table. It means it's date time. I can venture a guess that date

                time is continuous when you drag it into the columns shelf and that because

                it technically runs through ever minute and second it is showing all the

                values. To convert it to days you may be able to right click and change

                data type of you can wrap it in a DATE() function.

                 

                On Thursday, January 14, 2016, Tomek Zbrozek <

                1 of 1 people found this helpful
                • 5. Re: Unable to Show Missing Values (Dates) when date level is switchable using parameter
                  Joe Mako

                  In order for "Show Missing Values" (aka Domain Completion Data Densification) to be enabled we need something that I call a Range Aware Pill. A Range Aware Pill is a pill that has a Max Value, a Min Value and a Interval. Fields that are the data type Date or Bin will always be Range Aware, and DateTime will be as long as it is not configured to Exact Date in its context menu.

                   

                  Since you have correctly found that a Date data type will not work for your situation because we cannot set the pill configuration from a formula, the other option is to use a Bin turn on "Show Missing Values".

                   

                  To create a Bin field, we first need a number field, so we can expand on your formula logic to create a field I called "Date Number":

                   

                  CASE [Select date level]
                  WHEN "Day" THEN DATEDIFF('day',DATETRUNC('day',{MIN([Date])}),DATETRUNC('day', [Date]))
                  WHEN "Week" THEN DATEDIFF('week',DATETRUNC('week',{MIN([Date])}),DATETRUNC('week', [Date]))
                  END
                  

                   

                  This uses an LOD calc to get the min date in the data (if you want filters to affect the min date, they will need to be context filters), and the current mark's distance from that date, in either days or weeks depending on the parameter.

                   

                  Then right-click that field in the Data Window, and select Create->Bins, setting the Size of Bins to 1

                   

                  Next create a calculated field to fill in the dates, I called it "Date Filled":

                   

                  CASE [Select date level]
                  WHEN "Day" THEN DATEADD('day',INDEX()-1,DATETRUNC('day', TOTAL(MIN({MIN([Date])}))))
                  WHEN "Week" THEN DATEADD('week',INDEX()-1,DATETRUNC('week', TOTAL(MIN({MIN([Date])}))))
                  END
                  

                   

                  This is similar to the other one but now takes that same min date used to calculate the Date Number/Bin and turns that number back into a date. Since it is a table calc it will be evaluated after Densification.

                   

                  Then you can place the "Date Number (bin)" field on the Marks card, and the "Date Filled" field on the Rows shelf, and configure its compute using to "Date Number (bin)".

                   

                  Then when you place a pill for COUNTD(User ID), you can format the Pane Special Values Marks to be "Show at Default Value"

                  format.png

                   

                  Modified workbook attached, please let me know if you have any questions, thank you!

                   

                  P.S. If you are interested in Data Densification, check out Tableau Request Live - Data Densification on Vimeo

                  3 of 3 people found this helpful
                  • 6. Re: Unable to Show Missing Values (Dates) when date level is switchable using parameter
                    Joe Mako

                    You could also turn the references

                     

                    {MIN([Date])}

                    and

                    TOTAL(MIN({MIN([Date])}))

                     

                    into a parameter or a better yet constant like #2015-01-03# if the min date will not be changing to make for a faster evaluation.

                    1 of 1 people found this helpful
                    • 7. Re: Unable to Show Missing Values (Dates) when date level is switchable using parameter
                      Tomek Zbrozek

                      Thank you Joe, this is great. I think that not only I am impressed!

                      Cheers,

                      T.

                      • 8. Re: Unable to Show Missing Values (Dates) when date level is switchable using parameter
                        Yuriy Fal

                        Hi all,

                         

                        Albeit too late for an answer, anyway ...

                        There could be a simpler way to create a range-aware date field:

                         

                        date( datetrunc( [Choose date granularity], [Changed At] ) )

                         

                        The above field is of DATE type, so it is range-aware

                        right from the lowest "Exact Date" level (which is DAY, of course).

                         

                        The second subtle change could be to lower-case your [Choose date granularity] Parameter values,

                        so they're read as 'day', 'week' etc. Doing this allows to reference the Parameter directly

                        in the above calculation, thus to avoid CASE statement altogether.

                         

                        Of course, one could then proper-case Aliases in the Parameter, if needed.

                         

                        Yours,

                        Yuri

                        3 of 3 people found this helpful
                        • 9. Re: Unable to Show Missing Values (Dates) when date level is switchable using parameter
                          Joe Mako

                          Yes, correct on both points Yuri, there just needs to be a bit of extra logic for when a mark is shown, added in the "Number of users" formula below.

                           

                          Here is the workbook with your ideas implemented, thank you!

                           

                          Calc field for "Date_parametrized" on the Marks Card:

                          DATE(DATETRUNC([Select date level], [Date]))
                          

                           

                          Calc field for "Date Filled v2" on the Columns shelf:

                          DATEADD('day',INDEX()-1,TOTAL(MIN([Date_parametrized])))
                          

                           

                          Calc filed for "Number of users" on the Rows shelf:

                          IF DATETRUNC([Select date level],[Date Filled v2])=[Date Filled v2] THEN ZN(COUNTD([User_id])) END
                          

                           

                          The table calc's compute usings are set to "Date_parametrized", and the format of the "Number of users" pill in the Pane Special Values Marks is set to "Hide (Connect Lines)"

                          3 of 3 people found this helpful