1 2 Previous Next 28 Replies Latest reply on Aug 19, 2015 1:39 AM by Simon Runc Go to original post
      • 15. Re: Fixed LOD - value not "duplicating" as expected when looking by dimensions
        Nicole Edmonds

        Yup, All of those references have been EXTREMELY helpful when it comes to LOD.  I think the Viz Talk would be extremely helpful in drilling into the detail on the benefits of using one vs. another. 

        • 16. Re: Fixed LOD - value not "duplicating" as expected when looking by dimensions
          Rody Zakovich

          Yeah I think so too.

           

          Simon Runc I'm going to go ahead and get this started there.

           

          I'll ping Nicole and you in as well.

           

          Best regards,

          Rody

          • 17. Re: Fixed LOD - value not "duplicating" as expected when looking by dimensions
            Simon Runc

            ...Great plan! I've got to get on with some 'actual' work (boo!!!) today, but I'll definitely jump in when I'm done

             

            As you say apart from a few blogs, and official Tableau posts (all of the best you've detailed above), there isn't really a collection/discussion of all things LoD! I love your idea on creating calculations using FIXED, and then replicating with INCLUDE/EXCLUDE...a very good way to learn (and more importantly, understand, what's going on under-the-bonnet).

             

            I also came across this (following the Community Weekly Update)

             

            Calculated field to return the highest value

             

            which I've seen you helped out with some resources. I thought this was a very clever solution and shows the power a good understanding of the INCLUDE/EXCLUDE....

            • 18. Re: Fixed LOD - value not "duplicating" as expected when looking by dimensions
              Rody Zakovich

              Haha, yeah I need to get some work done too....but I have some time to get the discussion going, so I'll just go ahead and write it up now (Hope that's ok).

               

              I love this community!

               

              Rody

              • 19. Re: Fixed LOD - value not "duplicating" as expected when looking by dimensions
                Simon Runc

                ...Absolutely, and definitely give Bora a ping on this one too.

                • 20. Re: Fixed LOD - value not "duplicating" as expected when looking by dimensions
                  Rody Zakovich

                  Done and Done. Let me know if I should edit the post at all.

                  • 21. Re: Fixed LOD - value not "duplicating" as expected when looking by dimensions
                    Jonathan Drummey

                    @Nicole - did you ever get to your final goal?

                     

                    @Rody (and anyone else reading this) - the documentation about VizLOD is incorrect, the Pages Shelf is part of the vizLOD. Here's why:

                     

                    My own description of the Pages Shelf is that it adds a third "dimension" to the view beyond what we get from Rows and Columns, so using the Pages Shelf we can "slice" across whatever discrete(s) are on the Pages Shelf. Or another way is that the dimensions on the Pages Shelf affect the VizLOD, while the Pages Shelf itself (based on whatever discrete pills are on it) affects what I think of as the "Display LOD". Some ways that dimensions on the Pages Shelf affect the VizLOD:

                     

                    - The query to the data source includes dimensions on the Pages Shelf in the GROUP BY. (For me what's in the GROUP BY is the biggest definition of what's in the VizLOD).

                    - If I put [myDim] on the Pages Shelf and ATTR([myDim]) somewhere in the view, ATTR([myDim]) will always return a single value of [myDim] no matter the other dimensions in the view because the [myDim] dimension on pages is setting the LOD. Otherwise ATTR([myDim]) would potentially return * based on the granularity of the view vis-a-vis the data.

                    - Dims on the Pages Shelf are available for table calculation addressing & partitioning.

                    - They are considered along with Rows and Columns for domain completion behaviors. (I first learned about this from Ross Bunker who was the original Tableau developer of table calcs, he told me that the Pages Shelf was a third dimension for domain completion).

                    - If we use Page Name in the Title or Caption we see just the page, however if we use the dimension we will see "dim1, dim 2, dim 3" or All, depending on the number of values, just like dimensions anywhere else in the VizLOD. (Though this is also true for dims on the Filters Shelf).

                     

                    Jonathan

                    • 22. Re: Fixed LOD - value not "duplicating" as expected when looking by dimensions
                      Rody Zakovich

                      Jonathan Drummey

                       

                      As always, thank you for contributing!

                       

                      I did not know that about the page shelf. But it completely makes sense to me now (Thank you).

                       

                      Regards,

                      Rody

                      • 23. Re: Fixed LOD - value not "duplicating" as expected when looking by dimensions
                        Nicole Edmonds

                        @Jonathan - yes, Simon's answer worked well for my scenario; I posted the image of the end result applied to my original data. (without actually revealing specifically what it is showing, heh)  That said, I am curious if the same result can be accomplished in different ways, i.e. besides the "EXCLUDE/FIXED" solution provided by Simon, or just and EXCLUDE as mentioned by Roddy.  I had to get back to work, so I couldn't noodle much more around it, but glad to see a Viz Talk going around the subject. 

                         

                        What would be extra-special-doubly-cool was if users could have a CALCULATE function, similar to what is in PowerPivot (DAX).  In my current scenario, you can filter a measure within the calculation=true (for example) then divide by the sum total (All), and it wouldn't matter how you slice it, the ratio would be correct.  And I know Tableau can use PowerPivot as a data source, it just isn't feasible with my current set-up.  

                        • 24. Re: Fixed LOD - value not "duplicating" as expected when looking by dimensions
                          Simon Runc

                          hi Nicole,

                           

                          I've not used PowerPivot, so not familiar with that function...However I've had a quick read and I'm sure we can replicate this in Tableau (I'm certainly up for the challenge anyway!!).

                           

                          From  the few examples I've seen it appears that this function allows you to have 2 values (in the same table) where they at different levels of filtering. For example you could have Total Sales in one column, and Sales for a particular day in another. From my, very brief!, R&D the examples were very specific (eg. calculate me a field of sales from Store A, on 21/05/2015)

                           

                          As a starter for 10 I've created this quick version of my understanding where we have a table showing Total Sales, and then we have next to it the Sales from the last day of the data (I'm calling this Yesterday's sales). as you can see I have no filters.

                           

                          This formula is

                           

                          IIF([Order Date] = {MAX([Order Date])},[Sales],0)

                           

                          so is a Row Level Calculation, evaluating if the Order Date is the same as the last day in the data, and then only populating those rows with the value. as we aggregate this in the view the SUM of this column is just the values from yesterday. We could also use parameter(s) in the evaluation to make it more flexible.

                           

                          Let me know if this is at all close to what you are thinking of!

                           

                          ...If we are getting somewhere I'll exercise my Ambassadorial powers!! (...well that is the main one!!) and branch off to a new question

                          • 25. Re: Fixed LOD - value not "duplicating" as expected when looking by dimensions
                            Jonathan Drummey

                            There are a number of ways to filter without affecting results, this post sums up the 3 major methods and then I've added a comment that talks about how FIXED LOD expressions add a 4th: http://www.theinformationlab.co.uk/2013/01/14/maintaining-percent-of-total-whilst-filtering/#comment-60501.

                             

                            Those are all using filters, then there's the filter-inside-a-calc technique that Simon demonstrated. We can embed record level calcs (coming from the data source or an LOD expression) inside regular aggregates, regular aggregates (which could be from a single source or secondary source(es) using a Tableau data blend) inside table calculations, and table calls inside other table calculations, so there's quite a bit of logic that can be done inside Tableau.

                             

                            Jonathan

                            • 26. Re: Fixed LOD - value not "duplicating" as expected when looking by dimensions
                              Nicole Edmonds

                              @Simon - That's actually pretty close to what I am thinking, it's more of translating the logic of DAX to the logic of Tableau LOD's.    I think I could work something ssimilarly in the True/False scenario, if it was actually 1 and 0 instead of the string true/false.  I'm pre-coffee here, but I'll see how that would work when I am not bogged down by my day job.  Thanks everyone for your input, it is extremely helpful. 

                              • 27. Re: Fixed LOD - value not "duplicating" as expected when looking by dimensions
                                Nicole Edmonds

                                Ok, so in Ochim's effort to teach me a lesson - I figured out that I didn't need an LOD after all, albeit a cool way to go about things.  I was still running into a problem when I tried include Model as part of the fixed portion of the calculation.  So instead, I modifed the T/F condition field to be 1 or 0, created a measure as IIF [T/F Condition]=1 then SUM([AMOUNT]) else 0 END.  Now it works no matter which way I filter my report or which dimensions I use.  Also, Simon helped in his Powerpivot example, to this conclusion.   

                                • 28. Re: Fixed LOD - value not "duplicating" as expected when looking by dimensions
                                  Simon Runc

                                  ...Excellent news. Yes there are usually many ways we can achieve the same result (we could have used Table Calculations here for example), or as I think you're alluding to here where you can just divide the calculation you've done above, by the measure with no conditions.

                                   

                                  btw once you have your T/F statement equated to a 1 or 0, you can just do SUM([Amount]*[T/F Condition])

                                  1 2 Previous Next