1 2 Previous Next 17 Replies Latest reply on Jun 3, 2018 12:33 PM by samba mandadi

    LOD from multiple Sources?

    Jonathan Mills

      I am working on a flag which indicates Storm or Routine from two different data sources, and am blending them into a third.  The issue that I'm running into is that I need to create a Level of Detail expression in order that it rolls up to the correct indicator, rather than asterisk(*).

      Per the attached workbook, Sheet 1 shows my flag when the 'Order' and 'Proj Def' fields are included, however when they are removed from the view, my flag (Storm/Routine/Other) rolls up to an asterisk (*).

      I've tried creating an LOD expression, however it indicates that all fields in an lod expression must be from the same data source.

      Any help would be greatly appreciated.

        • 1. Re: LOD from multiple Sources?
          Simon Runc

          hi Jonathan,

           

          So you are correct that you can't use LoDs over Blended data, and as you say due to the different levels/grain in the various datasources you will need to have Order and Project in your VizLevelOfDetail in order for it to calculate correctly.

           

          So the way round this is to use a Table Calculation.

           

          In the tab 'SR - How it works' I've moved the Order and Project to the detail shelf (so they are still in our VizLoD), and created a WINDOW_SUM(SUM([Actual])) as our measure.

           

          I then set the table calc up using to the below

           

          Table Calc Setup 3.PNG

           

          and as you can see, we now get the right results (btw I also unticked the 'Ignore in Table Calculations' from your 'Storm/Routine/Other' as we want the WINDOW_SUM to create a new sum for each of these)...However, due to the specified VizLoD, we have multiple marks.

           

          On the 'SR - Only Return 1 Mark' tab, I used an index() (set up in exactly the same way as above), and brought to the filter shelf, and set it to 1. What this does is number each mark, and so filtering on 1 only leaves the 1 mark. As index() is a Table Calc, using it as a filter, filters the view but not the underlying data (below is the order of operations for Tableau, and you can see Table Calc filters are applied last)

           

          Order of Operations.png

           

           

          The only outstanding issue is the Grand Total...we have the right result, but this isn't filtered by our index()=1. This is due to Totals being calculated from the Level above the Viz. I've got to be honest here and say I don't know how to do this!! (just pinging the master!! Jonathan Drummey do you know how we can only return 1 mark for the Total? - I can't think of a way). As an interim solution, and I don't really like this, I've set the stack marks [in the analysis menu] to off. This places each mark on top of one another, which is why the Total is bolder.

           

          As you can see this is pretty complicated, so it might be easier/quicker in the long run to think how you could structure your data so you can get it all into a single source. When faced with this kind of problem, I'm OK if I need to create a chart or 2 using this, but when I have to do this for the entire workbook/model, I find a few hours getting the data in the 'ideal' shape/structure is gotten back 10 times over (and makes Tableau life a bit more fun!!)

           

          Hope this helps explain what's going on, and this solution helps. If not, or any of the above doesn't make sense, please post back

          • 2. Re: LOD from multiple Sources?
            Jonathan Mills

            Thanks so much for helping solve this issue... I must admit, your solution is a little above my skillset and took me a couple of times to figure out.

            Thanks again for your help.

             

            Jonathan

            • 3. Re: LOD from multiple Sources?
              Jonathan Drummey

              Change the Actual Window_Sum to IF FIRST()==0 THEN WINDOW_SUM(SUM([Actual])) END. Then Ctrl+Drag a copy of the Actual Window Sum on top of the Index pill on the Filters Shelf and set the new pill to Filter for Special->non-Null values:

               

              2015-09-25 13_37_48-Tableau - Trouble Storm - SR.png

               

              The reason why the INDEX() filter doesn't work is that table calc filters don't apply to grand totals and subtotals (those are in a separate computation filter.

               

              To work around that the solution embeds the filter in the calc using IF FIRST()==0. (INDEX==1 or LAST()==0 would also have worked). That makes the calc return a single non-Null result per partition so it works in both the detail and Grand Total rows. With Analysis->Stack Marks->Off for this view using either the INDEX() filter or the calc as a filter isn't all that necessary, the main reason why I do it is because it prevents Tableau from drawing unecessary marks in the detail Rows, this can improve performance when there are a lot of Null values (like if you had 10s of thousands of orders). I also tend to use calcs like this with IF FIRST()==0 or IF LAST()==0 instead of having an additional INDEX() calc because for me conceptually and mechanically it's simpler to get the one calc's Compute Using settings right and then re-use that calc in the Filters shelf.

               

              I've attached the workbook.

               

              Jonathan

               

              PS: I agree with Simon on his recommendation to do the pre-work to get your data set as flat as possible, this particular solution requires knowledge of aggregation, data blending, table calculations, use of an undocumented feature (Ignore in Table Calculations), grand totals, and how Tableau lays out data & stacks marks, all to generate a "simple" grand total.

              1 of 1 people found this helpful
              • 4. Re: LOD from multiple Sources?
                Simon Runc

                Thanks a lot Jonathan...I knew you'd know!!

                 

                I didn't really think there was much difference in using the IF FIRST()=0 and the INDEX()=1, so very good to know, and thank you for the explanation of why (from both a mechanical and conceptual perspective!).

                • 5. Re: LOD from multiple Sources?
                  Alexander Mou

                  Jonathan Drummey

                   

                  You said "The reason why the INDEX() filter doesn't work is that table calc filters don't apply to grand totals and subtotals (those are in a separate computation filter."

                   

                  What is this separate filter? (I'm getting into filters these days.)

                   

                  Simon Runc

                  Just updated the order of operations chart a bit:

                  Vizible Difference: Sets and the Precedence of Filtering

                  • 6. Re: LOD from multiple Sources?
                    Jonathan Drummey

                    Sorry about that, the “filter” in “separate computation filter” shouldn’t have been there. An editing mistake on my part.

                     

                    Jonathan

                    • 7. Re: LOD from multiple Sources?
                      Alexander Mou

                      Jonathan Drummey

                      I understand how Tableau calculates Grand Totals.

                      But this still eludes me: How does Tableau calculate Sub Totals?

                      • 8. Re: LOD from multiple Sources?
                        Jonathan Drummey

                        Hi Alexander,

                         

                        There are currently two kinds of grand totals & subtotals in Tableau - automatic totals and two-pass totals. Two-pass grand totals & subtotals are an aggregation of the marks in the current context (so the first pass is the regular aggregate that’s part of creating the marks, the second pass is the aggregate of the aggregate that creates the average of the sum of sales, etc.). Two-pass totals are limited to regular aggregates coming from primary or secondary sources, they don’t currently support table calcs or what I call blended aggregates (calcs that use measures from two or more data sources at once). When two-pass totals work, they work just like we’d want them to, summing, averaging, etc. the set of marks that we see.

                         

                        It’s the automatic totals that give people fits and lead to very long blog posts because they are separate computations at the appropriate level of detail for the given subtotal or total. An example is probably good, I did a blog post at http://drawingwithnumbers.artisart.org/lod-expressions-and-separate-custom-grand-totals-for-rows-and-columns demonstrating that turning on on Row & Column grand totals results in up to 4 levels of detail in the view: the “original" level of detail for the detail rows, the Rows Grand Total, Columns Grand Total, and finally the combined Row & Column Grand Total cell that shows up in the right-hand bottom corner (at least until Tableau allows users to put grand totals on top or left, which has been a surprisingly (to me) popular feature request). Within that framework, we can see that each additional subtotal potentially adds yet another level of detail to the view. Depending on the measures involved, Tableau may or may not issue additional query(yes) to the data source for each automatic total or subtotal (assuming nothing has been cached). For example if you’re using a non-additive aggregate measure like COUNTD() then Tableau will be definitely be issuing additional queries because COUNTD() needs to be recomputed for each level of detail in order to be accurate.

                         

                        Does that answer your question?

                         

                        Jonathan

                        • 9. Re: LOD from multiple Sources?
                          Alexander Mou

                          Thanks Jonathan. Sorry, I didn't make it clear.

                           

                          In the calculation of grand totals, we can do something like this

                           

                          if attr(month)=attr(month) then sum(sales)

                          else sum(sales)+1000

                          end

                           

                          This way we can manipulate the calculation of grand totals.

                          I wonder how we can do the similar for sub-totals. Or there is no way to do it.

                          • 10. Re: LOD from multiple Sources?
                            Jonathan Drummey

                            Hi Alexander,

                             

                            Sorry for the late response, going through some old notifications. Yes, you can use that technique to manipulate subtotals, I covered the basics in http://drawingwithnumbers.artisart.org/customizing-grand-totals-part-2/. Here's a quick example from Superstore:

                             

                            2015-11-05 13_47_27-Tableau - custom subtotal.png

                             

                            and here's the Custom Subtotal... calc:

                             

                            //detail row
                            IF MIN([Order Priority]) == MAX([Order Priority]) THEN
                                SUM([Sales])
                            ELSE
                                //subtotal row
                                IF MIN([Ship Mode]) == MAX([Ship Mode]) THEN
                                    -1
                                //grand total row
                                ELSE
                                    -2
                                END
                            END
                            

                            Notice that we have to be careful of the particular level of detail (grain) of each of the three computations - the detail rows that have a specific Ship Mode & Order Priority, the subtotal rows that are across Order Priorities for a given Ship Mode, and finally the Grand Total row that is across all Ship Modes & Order Priorities.

                             

                            Jonathan

                            • 11. Re: LOD from multiple Sources?
                              Alexander Mou

                              Great! Thanks a lot, Jonathan!

                               

                              On Thu, Nov 5, 2015 at 10:51 AM, Jonathan Drummey <

                              • 12. Re: LOD from multiple Sources?
                                Alexander Mou

                                Jonathan Drummey

                                Could you please have a look at this?

                                https://public.tableau.com/views/TopNVendorsFromSetsv2/FIX?:embed=y&:display_count=yes&:showTabs=y

                                 

                                I tried to manipulate the grand total, but it doesn't seem to budge.

                                This is from another post. You can reply there directly.

                                Grand Total Does NOT Update With Top Filter from Calculated Field

                                • 13. Re: LOD from multiple Sources?
                                  Alexander Mou

                                  I have tried if first()=0 trick and it doesn't work.

                                  Something fundamental I do not get yet.

                                   

                                  I read your article part-2 again. I didn't seem to uncover anything yet.

                                  Having a table calc as the grand total is always somewhat troubling.

                                  • 14. Re: LOD from multiple Sources?
                                    Daniel Vincent

                                    I took a look at it and created a custom grand total for Invoice Amt.  Substitute 1 for whatever value or calc you want.

                                     

                                    IF LAST()=FIRST() THEN 1

                                    ELSE SUM([Invoice Amt]) END

                                     

                                    Screen Shot 2015-11-06 at 8.47.36 PM.png

                                    1 2 Previous Next