10 Replies Latest reply on Jul 24, 2016 2:36 AM by Yuriy Fal

    Comparing Level of Detail Expressions Over Multiple Data Sources?

    d Vermaak

      Hello everyone,

      I have a calculated field that is being broken down by 2 dimensions for a specific company: Region and Year.

      What I would like to do is display this breakdown in comparison to the entire company.

       

      Here's what I mean...

      This is the breakdown by Region and Year:

       

       

       

      This is the total company breakdown (excluding regions):

       

       

      So, say for instance on the West Region, I'd like to show the data as this:

       

      RegionYear
      Region Calculated Field
      Company Calculated Field
      WestFY 20136.47.2
      FY 20148.88.2
      FY 201510.58.6
      FY 201610.59.5

       

       

      I am really not sure how to do this though.

      Maybe through a FIXED clause? Although this calculated field is working off multiple data sources so I am not sure if that would work.

       

      Also, please note that this is just a small sample of the data. I don't want to write out a calculated field for each region, because in reality there's not just 6, there's actually about 700. So specifying each region name in the calculated field would be far too much.

       

      Let me know if you have any questions. I'm sure I didn't do a great job in explaining this.

       

      Any help would be much appreciated!

        • 1. Re: Comparing Level of Detail Expressions?
          Ivan Young

          Hi Darren,

          LOD would be a good way to accomplish this.   For the Company calculated field create a custom date field for year then use it in an LOD.  {Fixed Year : SUM([Your Measure]) }.  You don't really need a LOD  for Region if you plan on keeping Region and Year in your view.

           

          Regards,

          Ivan

          • 2. Re: Comparing Level of Detail Expressions?
            d Vermaak

            Hmm I must've done this incorrectly.

            To explain:

            • I have 2 data sources.
            • Data source 1 contains employee hours
            • Data source 2 contains employee injuries
            • On data source 1 I've created a calculated field called Incident Rate. This is = (sum([data source 1].[employee injuries]) *200000)/sum([hours]). This is my original calculated field done through data blending.
            • To create the LOD as you've mentioned, I need to create a fixed calculated field on each of the data source? Otherwise, if I just try and do {Fixed Year : [Incident Rate] }, I get an error message that "All fields in LOD must come from the same data source".
              • So I create {Fixed Year : sum([Hours]) } on data source 1
              • And {Fixed Year : sum([Employee Injuries]) } on data source 2

            But even when I try and create a new calculated field based on these 2 new fixed calculated fields, I get an error that "Cannot blend the secondary source because one more fields use an unsupported aggregation".

            • 3. Re: Comparing Level of Detail Expressions?
              Naveen Agarwal

              Hi Darren:

               

              I was looking at your workbook and thinking about the same problem that data is coming from two different sources. I think you will need to perform the whole calculation (using the formula that you have in your Incident Rate Measure) in an LOD calculation.

               

              It sounds like you want to see the data over each FY comparing actual rates by region to the national rate for that FY. Here is a screen shot of what I could do by simply changing the order of Dimensions on the Rows Shelf. I was pleased to see that the national average values matched  exactly with what you provided in your original question.

               

               

              I think you could try to recalculate the national average using your two data sources in an LOD expression.

              • 4. Re: Comparing Level of Detail Expressions?
                d Vermaak

                I think I get what you are saying.

                I created this field to calculate the national average. This is taking the LOD expressions I've created on each of the data sources:

                =(SUM([LossRun (Corvel 7-5-16 at 2)].[Fixed OSHA Injuries])*200000)/sum([Fixed Hours])

                 

                 

                However, I get that same error: "Cannot blend the secondary source because one more fields use an unsupported aggregation".

                 

                Is this what you were talking about?

                • 5. Re: Comparing Level of Detail Expressions?
                  Naveen Agarwal

                  Can you post the revised workbook?

                  • 6. Re: Comparing Level of Detail Expressions Over Multiple Data Sources?
                    d Vermaak

                    Nothing has been revised. This calculation was included in the original workbook i posted..

                    • 7. Re: Comparing Level of Detail Expressions Over Multiple Data Sources?
                      Naveen Agarwal

                      Is there a way you can extract your data and include in the workbook? I will try to take a look at the calculations.

                      • 8. Re: Comparing Level of Detail Expressions Over Multiple Data Sources?
                        Yuriy Fal

                        Hi Darren,

                         

                        As Naveen Agarwal mentioned above,

                        the right values are calculated on a view

                        (as Tableau automatic sub-Totals)

                        when putting [Region Name] to the far left on Rows.

                        So it is a clue that a (proper) Table Calculation is built.

                         

                        What such a Table Calc may look like?

                        Given that the main aggregate calculation [Incident Rate]

                        is as simple as SUM() divided by SUM(),

                        it's Table Calc analogue would be as simple as

                        WINDOW_SUM() divided by WINDOW_SUM().

                         

                        So here it is (and the Addressing is along [Region Name], of course):

                        WINDOW_SUM( SUM([LossRun (Corvel 7-5-16 at 2)].[OSHA Recordable])*200000 ) /

                        WINDOW_SUM( sum([Hours]) )

                         

                        Please find the attached wb.

                         

                        Yours,

                        Yuri

                        • 9. Re: Comparing Level of Detail Expressions?
                          d Vermaak

                          Awesome thanks!

                           

                          Here's what I'm thinking though:

                          Region is actually part of a hierarchy that goes Region>District>Store. Ultimately what I was wanting to do was incorporate quick filters onto the dashboard so that the user can select whatever Region, District, or Store they want, and be shown not only that location's specific Incident Rate, but also how their Incident Rate is compared to the Company's Incident Rate.

                          Sorry, I should have clarified this in my original post.

                           

                          The problem with the solution you gave me is that if any filters are applied, the new window_sum calculation also gets filtered.

                           

                          All of this data is coming from excel workbooks, so I'm almost thinking it might just be easier to create a small table in excel  just like this:

                          and set up a relationship between this new table and the existing Incident Rate in Tableau. This doesn't seem like the smartest way to go about this, but I suppose it would work in theory...

                          What do you think?

                          • 10. Re: Comparing Level of Detail Expressions?
                            Yuriy Fal

                            Hi Darren,

                             

                            Using a materialised aggregate table(s)

                            to join to the main table would be a solution.

                            It ease a view and allow a drill-down hierarchy

                            Region --> District (State) --> Store.

                             

                            An alternative would be using table calculations.

                            This approach falls short when hierarchy is in place.

                            One could build a Parameter-driven dashboard for this

                            using a Sheet Swapping technique, but it is complicated, anyway ...

                             

                            Please find the attached with the latter.

                            Hope it helps to decide upon a simple layout that works.

                             

                            Yours,

                            Yuri