3 Replies Latest reply on Mar 26, 2019 8:24 AM by Barry OConnor

    Make a LOD calculation - then filter the data and do not include it in the extract

    Ivan Ivanov

      Hello!

       

      I am working on a Profit and Loss account and have come to the following issue:

       

      I have a FIXED LOD calculation that computes a result using data for a number of departments (each department's share of the administrative expenses), as follows:

      ( {FIXED Department Income} / {FIXED Total Income for all departments} ) * {FIXED Administrative Expenses}.

       

      Clarification: Department income only contains data for the selected department.

      Total income for all departments contains data for all departments.

      Administrative expenses only contains data for the Administration Department.

       

      Now we want to create separate reports (files - data extracts) for each department only including the relevant department's data (extract is filtered by Department; data comes from a published data source /Tableau Server/ containing all data). This way, the LOD calculation doesn't work as it lacks the data required for it's calculation. Hence, I am looking for a way to "save" the calculation results but I would prefer not the keep the rows behind it as I do not want a department to have access to other departments' results.

       

      Is there any way to do this? I am out of ideas and really hope that someone would help. I am okay to use Tableau Prep although I've never done so before and will have to get to know it for this particular issue.

       

      Please let me know if I can further clarify my issue.

       

      * I always mark the correct answer after my issue is solved *

       

      Thank you!

       

      Ivan

        • 1. Re: Make a LOD calculation - then filter the data and do not include it in the extract
          Barry OConnor

          Hi Ivan,

           

          This can be done easily in Tableau Prep.  Basically, you want to create aggregates of the three values you want in the calculation. To give you an example, I used Tableau's Superstore sample to aggregate Sales by Category:

          Then join the aggregation with the original file:

          That will provide you with the Sales Sum by Category.  You can use that same idea for your three calculations.  Once everything is joined together you can then create a calculation of the three fields to get the result you are looking for.  And Tableau Prep can then create the separate files you need.

          • 2. Re: Make a LOD calculation - then filter the data and do not include it in the extract
            Ivan Ivanov

            Hi Barry and thank you for the answer,

             

            Although this is a working solution it does not take in account the different periods but rather returns a result for the whole dataset. Obviously, I have failed to explain the whole picture and what we want to achieve.

            We tried to create a separate datasource to determine a coefficient and in my sql query I did the following:

             

            We group the data into periods, e.g. we have a dimension for each month/year included in the dataset (e.g. 01.Jan1.2018, 01.Feb.2018, 01.Mar.2018, etc.). Against this I am calculating a coefficient as follows:

            Total Administrative expenses / Total income for all departments and am returning the following data (example)

            Period (dimension) / Coefficient (measure)

            01.Jan.2018 / 0.1752

            01.Feb.2018 / 0.1684

            01.Mar.2018 / 0.1727

            01.Apr.2018 / 0.1798

            ...

             

            Now having this coefficient I will be able to multiply the Department income by it and will receive the department's share of the Administrative expenses. This way, even if the department manager opens the file in Tableau Desktop they will not see any other data but the coefficient (and of course, their department's data). So our idea is achieved - other department's data is secured and every department have access only to their own data.

             

            However, I am experiencing an issue with Tableau reading the code (sql). In MSSQL Server it takes a little more than a minute to return coefficients for the past 3 years but once we put in Tableau it freezes and counts for a long time withour returning a result (nor an error). My senior colleague will be working on creating a datasource with the coefficients and joining or blending them in Tableau.

             

            I will mark your answer as correct as it answers my question in the way I am asking it

             

            Barry, a last one: Can I achieve what I have explained here in Tableau Prep? Thanks!

            • 3. Re: Make a LOD calculation - then filter the data and do not include it in the extract
              Barry OConnor

              Ivan,

               

              Glad you found a solution. Yes, you can create the coefficient in Tableau Prep.  Create aggregations for both Total Administrative Expenses and Total Income, using Period as the grouping.  You can then use that to create a calculation for each department's administrative expenses.

               

              Barry