9 Replies Latest reply on Aug 2, 2016 12:35 PM by Phillip Black

    LOD Calculation mysteriously disappears

    Phillip Black

      I have an fixed LOD Calculation that mysteriously drops and sometimes appears blank. I can't for the life of me figure out why. Any ideas?

      change.png

      Workbook is attached - Thank you for any help!

        • 1. Re: LOD Calculation mysteriously disappears
          Dan Sanchez

          Hi Phillip!

           

          Thanks for uploading the workbook!  It looks like the LOD calc is based on a logic statement checking to see if [Days Since Install] = 0.  I pulled [Days Since Install] out onto the viz and got this:

           

           

          It looks like the LOD is returning NULL for a few days because [Days Since Install] is also NULL for those days.  Hope that helps!

           

          Thanks!

          1 of 1 people found this helpful
          • 2. Re: LOD Calculation mysteriously disappears
            Phillip Black

            hmmm this seems to be the right path! any suggestion for correcting this? I tried adding a Days Since Install filter to exclude null values, but that didn't seem to work.

            • 3. Re: LOD Calculation mysteriously disappears
              Phillip Black

              I also tried,

               

              SUM({FIXED [Install Cohort Day], [Channel Filter], [Country Filter], [Platform Filter], [Game Filter], [Publisher]: SUM([count])})

               

              But still having cohort sizes that moved between values - when it should be fixed.

              • 4. Re: LOD Calculation mysteriously disappears
                Simon Runc

                hi Phillip,

                 

                So the reason they are showing blanks, is due to (as Dan has said) that there is no data for some Days Since Install/Install Cohort Day combinations. So for example, in the below there is no data pertaining to Days Since Install = 119 & Install Cohort Day of 29th Feb 2016

                 

                 

                The only reason that combination is shown (with a blank cell) is the way the table is set out...it has to create a cell for each Days Since Install/Install Cohort Day Combination (as a table is a N x N matrix), even if one (or more) of those cells is blank. As you can see, where I've put everything down the page, I no longer have an entry for Days Since Install = 119 & Install Cohort Day of 29th Feb 2016

                 

                If we look at this example from superstore...no one in Arkansas is in the Corporate Segment...but when displayed like this Tableau has to create a cell for it, but can't populate it (as there is no data). If I were to bring segment into the rows shelf, there would no longer be a row for the  Arkansas/ Corporate Segment

                 

                 

                For full factual accuracy there is a little known (and even less documented) feature of Tableau known as data-densification where we can get these cells to populate...but it's quite complicated and don't think is necessary here.

                 

                Hopefully that explains why you are getting blanks, but if that doesn't make sense please post back.

                • 5. Re: LOD Calculation mysteriously disappears
                  Phillip Black

                  I had this revelation last night! If this is the case what do you think are the solutions? All I need are the values on Days Since Install = 0 to remain constant and only vary when the user selects Country for example.

                  Data-densification looks like a complex solution, do you think there is a better one?

                   

                  Thank you for the help, I really appreciate it!

                  • 6. Re: LOD Calculation mysteriously disappears
                    Simon Runc

                    hi Philip,

                     

                    So one option (and this will depend on being able to alter the view/data feeding Tableau) would be to densify your data before bringing it into Tableau. So creating every Day/DaysSinceInstall combination?

                     

                    The other way is to use densification within Tableau. Although this is complicated to understand the whens/why Tableau densifies data, the actual technical implementation here isn't too bad. Here is a great video on the subject, and an accompanying Google Doc detailing when it takes place

                     

                    Tableau Request Live - Data Densification on Vimeo

                     

                    So in your version, we 'activate' densification by bringing a Table Calculation (I've just used index()) onto the detail shelf. As you have 2 opposing blue pills (Row and Column), and set this to Compute Using = DateSinceInstall. This densifies the data, so you 'gaps' can now be accessed (they have values there not blanks...although the values are still  NULL. btw Once we are done we can actually remove this calculation, as we have a table calculation in our final formula, but wanted to explain the process

                     

                    So we can then create a formula that looks for these NULLs and populates them with the Previous Value...So our formula becomes

                    [Cohort Size (Day) - DENSE]

                    IF ISNULL([Cohort Size (Day)]) THEN PREVIOUS_VALUE([Cohort Size (Day)])

                    ELSE [Cohort Size (Day)]

                    END

                     

                    I then set this to compute using = DateSinceInstall

                     

                    for clarity I've also added a colouring calculation so you can see which are 'Real' values and which are 'Densified-generated'

                    • 7. Re: LOD Calculation mysteriously disappears
                      Phillip Black

                      Hey Simon,

                       

                      Thank you so much for the help, this definitely helps solve the null problems, but we still have cohorts dropping out.

                      cohort drop 2.pngcohort drop.png

                       

                      When you view the underlying data, on day 44 the paid group did not come back thus cause a drop (but was there on 45). (Ignore the version numbers). I need that paid group to show up on day 44.

                      • 8. Re: LOD Calculation mysteriously disappears
                        Simon Runc

                        hi Philip,

                         

                        So the issue here is the data...any  calculation can only run over the data it has!

                         

                        So to force this consistency, we can put handlers in for when the data changes row to row...in the attached I've added in a condition to check the row above is the same as this row's calculation, and if it's not it overrides it with that calculation

                         

                        [Cohort Size (Day) - DENSE + Ensure Consitency]

                        IF ISNULL([Cohort Size (Day)]) OR [Cohort Size (Day)]<>LOOKUP([Cohort Size (Day)],-1)

                        THEN PREVIOUS_VALUE([Cohort Size (Day)])

                        ELSE [Cohort Size (Day)]

                        END

                         

                        now this works fine, until you have 2 (missing data) in a row...

                         

                        So an alternative method, is to take the WINDOW_MAX so we can run this over Days Since Install, and it will only return the MAX for all columns...

                        [Cohort Size (Day) - DENSE + WINDOW_MAX]

                        IF ISNULL([Cohort Size (Day)]) THEN PREVIOUS_VALUE([Cohort Size (Day)])

                        ELSE WINDOW_MAX([Cohort Size (Day)])

                        END

                         

                        Obviously this requires that the MAX value is the correct one (as it seems the consistency goes when data is missing, this seems a fair assumption)

                         

                         

                        On both of these options I've coloured where the Data is 'generated' (ie a NULL) or changed (ie. overridden the value that would have been returned normally)

                         

                        Hope that helps

                        1 of 1 people found this helpful
                        • 9. Re: LOD Calculation mysteriously disappears
                          Phillip Black

                          This is such a simple and clever solution - I was thinking of joining a table with every possible combination! This works much better and scales in a far better way. Can't thank you enough for your help.