10 Replies Latest reply on Jan 17, 2019 2:38 PM by Daniel Lawrence

    Time Periods with no Entries throwing errors

    Daniel Lawrence

      Hello,

       

        I have a viz on my dashboard that has data entries on an infrequent schedule.  I have a relative date filter applied to it and when the "This Year" value is clicked, the Viz has nothing to show as there are no 2019 entries yet and it throws an error and shades out the viz.

       

      How can I protect against this and have it just show up as 0?

       

      These are not Null Entries as i would simply be able to use the ZN() function but there is a lack of entries.

       

      FYI, I have JSON data so padding the data in anyway would be difficult to achieve.

       

      Thank you for any and all insight!

       

      Daniel 

        • 1. Re: Time Periods with no Entries throwing errors
          Deepak Rai

          TRY :

          IFNULL(LOOKUP(SUM(Measure),0),0)

          • 2. Re: Time Periods with no Entries throwing errors
            Daniel Lawrence

            Deepak,

            Thanks for the response!

            No luck though, unfortunately. Maybe because of the relative date filter, would it not have any rows to look to?  I think the table calculations happen after dimension filters and measure filters in the order of operations.

            • 3. Re: Time Periods with no Entries throwing errors
              maneesh.gaddam

              Why is it throwing an error? Viz should be Empty with 0 marks, right?

              • 4. Re: Time Periods with no Entries throwing errors
                Daniel Lawrence

                I would think that would be the case but its not.

                 

                "Unable to complete action

                Internal Error - An unexpected error occurred and the operation could not be completed.

                Internal Error - An unexpected error occurred and the operation could not be completed."

                 

                The error appears to be thrown at the worksheet level.  I have a data-blended filter on it so that may be the reason but I do not see why that would throw an error as the date ranges on both data sources are very similar if not exact.

                • 5. Re: Time Periods with no Entries throwing errors
                  Daniel Lawrence

                  I have attached a modified version of the problem.  I believe I have kept all the problematic portions in as the error persists.

                   

                  • Revenue A and Revenue B are coming from different data sources.
                  • Location A and Location B are the same across both Revenue sources, Location C exists in Revenue A but not in Revenue B an Offsite exists in neither but needed to be artificially created by renaming some entries from Location A or Location B.  This was very easy to accomplish in Revenue A but for various reasons impossible to accomplish in Revenue B.  So it was acceptable to "copy" Offsite from Revenue A to Revenue B, so I duplicated the Revenue A Call Out worksheet, filtered out Location A, B,C and called it Offsite Stand In and changed some formatting.  Needless to say, the two Offsite values should mirror each other. 
                  • The Date Range filter is from revenue B but has no problem working on Revenue A or Revenue A Call Out, so I assume it is working on Offsite Stand in which is just a duplicate of Revenue A Call Out.  Not sure if this assumption is the core of the problem.
                  • Any time prior to 2019 Offsite Stand In has values, but it does not yet have any 2019 values so This Year throws the error.  Oddly enough, sometimes if the default is an acceptable time range (e.g. previous 3 years and the first change on the date filter is to This Year, then Offsite Stand In will collapse as expected but still throw the error.  Then if you change it to Previous 3 years and then back to This year, it will throw the error and shade out the Offsite Stand In Viz without collapsing.

                   

                  Joe Oppelt, I saw that you were able to solve a much less complex version of this problem in this thread.  Do you have any insight into this situation?

                   

                  Any and all insight is appreciated!

                  Thank you

                  • 6. Re: Time Periods with no Entries throwing errors
                    Joe Oppelt

                    I can't explain it.  I would expect the sheet just to go blank, and I recreated the same sheet (roughly) and that one behaves as I expected.

                     

                    I also messed with [Dates for Date Range].  Your calc essentially ends up making the equivalent of [Date].  Right now that's what's in there.  (Just [Date].)  I thought somehow that was doing something.  But your sheet chokes with that too. I don't get it.

                     

                    There's something different between the two sheets.  Or maybe your sheet is somehow corrupted.  Consider taking my sheet.  Format it exactly the way you want, and run with that.

                    • 7. Re: Time Periods with no Entries throwing errors
                      Daniel Lawrence

                      Thanks!  I will take a look at it and see if i can ferret out the differences.

                      • 8. Re: Time Periods with no Entries throwing errors
                        Daniel Lawrence

                        Joe,

                         

                            I got it to work and collapse as needed without throwing errors.  I think it was a combination of two things:

                        • The Date filter was from Revenue B and for some reason it was not liking it, so I was able to successfully put all the sheets (from both data sources) on a Date filter from Revenue A and none of the sheets had any problems with it.  This is what worked in your edited version.
                        • When I took it the original dashboard, I was still getting an error, so i think it probably was some weird corruption/formatting snafu that Tableau did not like, so I just rebuilt the small portion of the dashboard that the worksheet was located and put in the new cleaner worksheet and made all the sheets conform to the Date filter from Revenue A.

                         

                        After those two things it worked!  Thank you for your help!

                        • 9. Re: Time Periods with no Entries throwing errors
                          Joe Oppelt

                          Well I can't explain why.  But getting it working is what you really need, I presume.  And now that you have it, you can flip off the old friends who were stabbing you in the back, and party with new friends.