9 Replies Latest reply on Sep 29, 2016 1:58 AM by Yuriy Fal

    How to make a calculation made in the database being recalculated in each filter

    Manuel Rodriguez
    Manuel Rodriguez Jun 20, 2016 5:52 AM

      Hi everyone,

       

      I attached my database and a Tableau file in order to help you to illustrate the possible solution you may find to my problem. Thank you in advance for taking the time on reading this and trying to help me.

       

      Sorry if you find the title is very vague, but I could not find a better one to explain my situation. I will try to illustrate better my problem with the following comments:

       

       

      I have a data composed by 4 different Financial Items: 1-Accounts Receivable (AR), 2-Past dues (PD), 3-Sales and 4-Days Sales Outstanding (DSO).  The data is divided by country, sub-region and region. I have data for this year and the previous one. My idea is to compare the variations of these Financial Items


      I took the Sales, Past dues and AR from my datasource and then I made the calculation of DSO in the excel file at country level following this formula: DSO=  AR/Sales.

       

      My problem appears when I select a "high level" view. For instance, if I take the Regional view, the DSO is calculated adding all the individual DSO of each country member of that region rather than taking the total amount of AR and Sales and applying the formula. The results are big values that do not make sense with the reality (see the picture below)

       

       

      As you can see, is not possible to have a DSO of 21k in EMEA.

       

      After uselessly trying to solve this issue, I decided to Create measures of each Financial Item in order to be able to solve this calculation issue. Well, I was able to do so, however i could not add the calculations vs PY$ and vs PY% to each of them because is not possible to apply measures to other measures. Hence, I got the real DSO but I have now the problem of not having the variances Actual vs Pior Year (See the picture below):

      So basically my problem is that I cannot get both the correct DSO and the variations vs PY$ and vs PY% in the same table. I don't know if manipulating my database I could do it but I could not think any way to do it. Please, do not hesitate on asking for more info or if you have any doubt.

       

       

      Thank you very much

       

       

       

      Manuel

        • 1. Re: How to make a calculation made in the database being recalculated in each filter
          Yuriy Fal
          Yuriy Fal Ambassador Jul 2, 2016 5:18 AM (in response to Manuel Rodriguez)

          Hi Manuel,

           

          Something like this?

          Please find the attached.

           

          Yours,

          Yuri

          • 2. Re: How to make a calculation made in the database being recalculated in each filter
            Manuel Rodriguez
            Manuel Rodriguez Jul 4, 2016 2:00 AM (in response to Yuriy Fal)

            Hi Yuriy,

             

            First of all I would like to thank you your answer. I was losing the hope with this... hahaha.

            Your file is better than mine because you can at least show everything I wanted to show. However, is not exactly what I was looking for.


            I am looking for a similar structure to the "DSO Acumulated" tab in the book. 4 Financial Items: AR- PD - Sales - DSO(where I have the problem) and for all of them i want to show  4 columns with the following Measures: ACT- PY - vsPY$ and vs PY%
            In my database (also attached), I have the DSO calculated for each country. The problem appears  when I tried to show a higher view (By Region for instance) Then the DSO of each country member of a particular region is added. For instance the EMEA DSO is not the sum([AR])/sum([Sales])*30  is the sum of each DSO  conuntry in EMEA (Spain, France, Italy, etc.) therefore I ended up with a wrong value in ACT for EMEA (21486 days is not a reasonable figure).

             

             

             

            I tried then to make my DSO a measure using the following formula: sum([AR])/sum([Sales])*30 then, with this formula I was not able to add to this measure the other two that I calculated vsPY$ vs PY%. This is what I am showing in the "Variances Missing" tab. I dont know if its possible to have a measure in this case DSO and then apply to this one another two measures in order to get the vsPY$ and vsPY%.

             

             

             

             

            Hope this clarify my problem and what I want to do. Please, let me know if you need more information.

             

             

             

             

            Best Regards,

             

             

            Manuel

            1 of 1 people found this helpful
            • 3. Re: How to make a calculation made in the database being recalculated in each filter
              Yuriy Fal
              Yuriy Fal Ambassador Jul 4, 2016 6:44 AM (in response to Manuel Rodriguez)

              Hi Manuel,

               

              Please find the attached

              (w/mod on Sheet 7a).

              Please do not hesitate to ask

              any question you may have.

               

              Yours,

              Yuri

              1 of 1 people found this helpful
              • 4. Re: How to make a calculation made in the database being recalculated in each filter
                Manuel Rodriguez
                Manuel Rodriguez Jul 6, 2016 2:31 AM (in response to Yuriy Fal)

                Hi Yuriy,

                 

                Thank you again for your help. I am glad to say that this time you did exactly what I was looking for!

                I started using Tableau 2 months ago and I thought that this cannot be done. Thank you for showing me how to do it. This is gonna open many possibilities for me.

                 

                However, I would like to ask you a couple of questions in order to understand how these formulas works. I the picture below illustrates the 5 different formulas you have used:Yury.png

                 

                 

                 

                1- The "Calculation 1 and 2" gives you the ACT and the PY. But how is this formula identifying what is FIRST or what is LAST? I assume that is because of the two different date that every value has in my database right? April-15 and April-16.

                 

                2- I Would like to talk now about the YF : Metrics because is included in the other 2, therefore is necessary to explain this one in order to understand the rest.
                Using the ATTR function you apply to  FI= DSO the formula to calculate it meanwhile the Base $ value is used to calculate the rest.  Am I right?

                 

                3- YF Metrics vs PY% and vs PY$ these two works similar to the 1-2 calculations. Using first you take the Actuals and then you rest the PY with the Lookup + last function.

                 

                 

                Please let me know if I am missing something here, I think this can be also useful for other people who arrived here with the same problem.

                 

                 

                In addition , I would like to ask something else in order to see the limitations of the YF Metrics formula:

                 

                Lets assume that I would like to explain how do the AR changes during the period because of the changes in Sales and in DSO. If I wanted to know the impact that Sales and DSO have in the AR variances individually, could I do it?

                 

                For Instance: In order to calculate the AR variations due to DSO, I would need to take (ACTSales/30)*(ACTDSO-PYDSO) I am not sure if I can do that because I already used the ATTR function to calculate the DSO

                 

                 

                I tried creating a new Financial Item called "Due to DSO", then I tried applying the following formula but I got an error.

                 

                 

                 

                 

                 

                Do you think is a way to do it in tableau? Or Do I need to apply my formula in my excel file before using it in tableau? If I do the second I would only need to use the Financial Item with the formula already applied in Excel  and It would be OK. Besides, in this case it does not matter if some countries are clustered in a region because It make sense to add all the variations from each country in the region.
                However, I think the less I modify the database the better. I mark this topic as "Answered" however, I would like to know if I can go further in tableau with this formula.

                 

                 

                Thank you so much for your time and help. I hope this solution can help other users with the same problem.

                 

                Best Regards,

                 

                Manuel

                1 of 1 people found this helpful
                • 5. Re: How to make a calculation made in the database being recalculated in each filter
                  Yuriy Fal
                  Yuriy Fal Ambassador Jul 17, 2016 1:42 PM (in response to Manuel Rodriguez)

                  Hi Manuel,

                   

                  I'm sorry for being too late with my answers, anyway ...

                   

                  1- The "Calculation 1 and 2" gives you the ACT and the PY.

                  But how is this formula identifying what is FIRST or what is LAST?

                  I assume that is because of the two different date

                  that every value has in my database right? April-15 and April-16.

                  Right.

                   

                  Both Calculation 1 and 2 are Nested Table Calculations,

                  as they're referencing another Table Calc [YF : Metrics].

                   

                  For a nested table calculation pill one should define

                  the Addressing (Compute using) and the Partitioning

                  for each (inner) calculation independently.

                   

                  So in this case the inner calculation (which is [YF : Metrics] )

                  is Compute using [Financial Item], whereas the outer ones --

                  those you've called Calculation 1 and Calculation 2 --

                  are Compute using [Date Type] pill (in Details on a view).

                  It means that the FIRST() and LAST() belongs to the

                  'ACT' and 'PY' values of [Date Type] dimension, respectively.

                   

                  2- I Would like to talk now about the YF : Metrics because is included in the other 2,

                  therefore is necessary to explain this one in order to understand the rest.

                  Using the ATTR function you apply to  FI= DSO the formula to calculate it

                  meanwhile the Base $ value is used to calculate the rest.  Am I right?

                   

                  Yes, you're right. The ATTR() is used because of the Table Calculation,

                  but it is effectively just doing if FI = DSO then ... else ... end

                   

                  3- YF Metrics vs PY% and vs PY$ these two works similar to the 1-2 calculations.

                  Using first you take the Actuals and then you rest the PY with the Lookup + last function.

                  That's right, too.

                   

                   

                  As for the last part of your message,

                  my answer would be a bitter-sweet

                  "yes, of course there are (limitations)" --

                  the one to be taken with a grain of salt

                  (actually a big chunk of it).

                   

                  The main difficulty with the approach

                  of tackling Excel-inspired problems in Tableau

                  by using (heavily nested) table calculations

                  is that data typically comes in a "wrong shape".

                   

                  In your particular case it is the [Financial Item]

                  that would be a problem, namely, it's 'DSO' records.

                  Since DSO by it's nature is an Aggregate --

                  SUM(AR) by SUM(Sales) -- it's Row-Level values

                  (materialized in the datasource [Base $] field)

                  couldn't be summed without loosing any common sense,

                  whereas other members of [Financial Item] dimension

                  are completely additive across any other dimension

                  (their [Base $] values could be summed up nicely).

                   

                  Making the right DSO numbers to go into the right place

                  requires Table Calculations, hence [YF : Metrics].

                  But this in turn creates it's own problems

                  when an extended logic should be built thereafter.

                  These by default should be table calcs on top of other table calcs --

                  and so on and on (hence all that nested table calcs stuff).

                   

                  One could even make your 'Variation Due to DSO' logic in Tableau

                  based on your datasource as is, but it could be tedious and error-prone

                  because of the problem with ill-shaped data.

                   

                  The way out of this would be re-shaping data.

                  In this particular case it could be as simple as

                  Un-Pivoting the [Financial Item] dimension.

                  With [AR], [PD] and [Sales] as distinct Measures

                  everything is going to be a natural fit for Tableau.

                   

                  Please find the attached.

                  Hope it make sense.

                   

                  Yours,

                  Yuri

                  1 of 1 people found this helpful
                  • 6. Re: How to make a calculation made in the database being recalculated in each filter
                    Manuel Rodriguez
                    Manuel Rodriguez Aug 23, 2016 5:40 AM (in response to Yuriy Fal)

                    Thank you so much Yury!

                     

                     

                    Sorry for answering so late but I was fairly busy this month and I did not have time to read carefully your post until now.
                    I am very happy to say that following your instructions, I modifed my database and now I am able to get the data I wanted.

                     

                    However, I am experiencing some Layout problems that you might be able to share some of your knowledge in order to help me to solve them.

                     

                     

                    First of all, when I try to color the whole table using the Square mark to show the different colors emulating a heatmap, I got this problem. This is caused by the element called "Data Type" allocated in the Marks box. However, I cannot remove this element because it is used by the calculations therefore I cannot get the layout I want without loosing my data.

                    Picture 1 Illustrates the "Heatmap" view I want to get, and picture number 2 illustrates the problem I have because of the Data Type element in the Marks box:

                     

                    Picture 1

                    Picture 2

                     

                    In addition, I split my table by Financial Item in order to color each of them according to the vsPY$ measure to quickly see where are the biggest variances in each of the Financial Items.(Picture below)

                     


                    Apart from the problem with the DSO mentioned above, I have another one:
                    If I want to drill down a little bit my data using the parameters "Select Region" & "Select Subregion"  I arrive sometimes to some Subregions with many countries and many of them has barely data.

                    I would like to create an Index leaving for instance the top 5 countries with highest AR and cluster the rest in a row call "other". My problem is double:

                    First, I have my data separated in different tables therefore I cannot apply the index to all of them using the AR because only one of them has this dimension in the data.
                    Second, when I apply the index for the first financial Item, it counts all the regions and subregions to do this index. In other words, I does not apply the index only for the data that is showed in a concrete view. For example, If I select a subregion with a smaill amount of AR, all the data is grouped as Other because the rank is made taking into account all the groups in the data.

                     

                     

                    Thank you again in advance,

                     

                     

                     

                    Best regards,

                     

                     

                    Manuel

                    • 7. Re: How to make a calculation made in the database being recalculated in each filter
                      Yuriy Fal
                      Yuriy Fal Ambassador Sep 13, 2016 5:20 AM (in response to Manuel Rodriguez)

                      Hi Manuel,

                       

                      With Top N by RANK() things quickly get complicated,

                      because of Table calcs on top of another Table calcs (and so on).

                       

                      Moreover, one could not even get Square Marks working properly,

                      because to get 'Others' values in a single cell would need Stacking Marks OFF.

                      So in this case one opt to use a simple coloured cross-tab instead.

                       

                      Please find the attached w/ "AR Ranked" sheet to explain.

                       

                      Yours,

                      Yuri

                       

                      IMHO, this is a point to calm and revise the dashboard layout,

                      the purpose of the analysis and the user experience because of that.

                      1 of 1 people found this helpful
                      • 8. Re: How to make a calculation made in the database being recalculated in each filter
                        Manuel Rodriguez
                        Manuel Rodriguez Sep 29, 2016 1:41 AM (in response to Yuriy Fal)

                        Hi Yury,

                         

                        Thank you for your answer. You gave me a crystal clear explanation as usual.

                         

                        I think you are right and it's time to think a little bit before continue developing the workbook. I will discuss with my colleagues what would be the best solution for our problem. Maybe we can split in two dashboards the information. Keep one with Region and Subregion level and give the chance to drill down even more in a Country dashboard which will show only that table.

                         

                        It is a shame that sometimes Tableau does not let you get the exactly view you want. But we always can adapt to the circumstances.

                        I will show you the new version with my solution once is finished.

                         

                        Thank you so much for your time and help. I have learned a lot with your explanations.

                         

                        Best regards

                         

                        Manuel