1 2 Previous Next 15 Replies Latest reply on Jun 3, 2016 8:53 PM by Joe Oppelt

    create calculated value based on other calculated values

    Jean-thomas Meyer

      Hi, so the part was solved:)

      bu tnow I have still an aggreation issue.

      When i put DK1DB on rows level an split by timetal (for) ( de 24 hours of a days) in the column ; it looks good.

      BUT i i remove the spilt then the total is around 16.000 while it should only be around 2000....

      what do I still do wrong ?

      thks

      jt

       

      Ce message a été modifié par : Jean-thomas Meyer Next step issue

        • 1. Re: create calculated value based on other calculated values
          Tom W

          It's impossible to say without seeing an example of your data and data structure. Please post a Tableau Packaged Workbook.

           

          Thanks!

          • 2. Re: create calculated value based on other calculated values
            Jean-thomas Meyer

            You are right

            here you go as you can see

            DK1 buy price and Dk1 buy volume do have data

            when i want to have a revenue  as DK DB = dk1buy price * dk1 buy volume, i get a 0

            • 3. Re: create calculated value based on other calculated values
              Jean-thomas Meyer

              done i have attached the doc in the original question thks

              • 4. Re: create calculated value based on other calculated values
                Tom W

                It needs to be a Tableau Packaged Workbook, otherwise it fails to open as I don't have access to your data.

                Click File > Export Packaged Workbook.

                • 6. Re: create calculated value based on other calculated values
                  Tom W

                  'DK1 Elbas Buy Price' and 'DK1Elbas  Buy Volume' both reference different values in the '[Kvantitet CD]' dimension. Thus, these two calculations will never occur in the same row.

                   

                  Your calculation DK1 DB is performing a row level calculation and thus, it will never evaluate to anything other than 0.

                   

                  You need to change DK1 DB to an aggregate function i.e. sum([DK1 Elbas Buy Price])*sum([DK1Elbas  Buy Volume])

                  • 7. Re: create calculated value based on other calculated values
                    Jean-thomas Meyer

                    This is great many thanks it works, and I think that I do actually understand ☺

                    But what happen if I get som more calculation like :

                    SUM(-[DK2 Elbas Buy Price] + )* (SUM())

                    +

                    (SUM( - )* Sum())

                     

                    It returns an error,

                    Should I always aggreate before each operator or ?

                    Thanks a lot again for your help. I guess you can see my challenges if you know a good step by step guide I ll love to read it

                    Thks a lot again

                    • 8. Re: create calculated value based on other calculated values
                      Joe Oppelt

                      Jean-thomas Meyer wrote:

                       

                      ...

                       

                      It returns an error,

                      ...

                      It would help if you pasted the error.

                       

                      My guess:  you hit the aggregate/non-aggregate error.  If so, wrap the non-aggregate variable in ATTR().  But that's just a guideline.  Sometimes you DO want to wrap it in SUM() or AVG(), etc.

                      • 9. Re: create calculated value based on other calculated values
                        Tom W

                        +1 to Joe - what's the actual error?

                         

                        If you're using the exact formula you're quoting, of course it won't work as you're using the SUM function for example with nothing in the parenthesis.

                        • 10. Re: create calculated value based on other calculated values
                          Jean-thomas Meyer

                          You are 100% right guys, it is my poor understanding of aggragation which is the root cause of the issue.

                          I have solved my issue now. If you have any good materiel around aggregation usage and deep understanding that would be great

                          Thanks a lot for you rhelp

                          Have a great day

                          JT

                           

                          Med venlig hilsen

                          Jean-Thomas Meyer

                          Team Lead

                          Trading & Reporting

                          Thermal Power

                           

                          DONG Energy

                          Tlf. +45 99 55 61 19

                          • 11. Re: create calculated value based on other calculated values
                            Joe Oppelt

                            Material on aggregation ....  A search through this forum, through Tableau's knowledge base, and through google-supplied links will swamp you with enough reading to last you the rest of your life.

                             

                            But let me take a stab at a quick synopsis.

                             

                            You can have row-level calcs.  For instance each row might have a sales amount and an expense amount.  Row-by-row you can figure out profit percent per expense with a calc that looks like this:

                             

                            ([Sales] - [Expenses]) / [Expenses]

                             

                            That would give you a profit percent per transaction.  That would not be an aggregate.  It would be calc'd at the row level.

                             

                            When you drag that calc onto the sheet you'll notice that Tableau does SUM([your calc]).  (You can always change it to AVG() or MIN() or whatever you need in a given situation -- just like any row-level measure.)

                             

                            Now you might put that on a sheet where you want to see for each account, the profit as a percentage of expenses by month.  So you create a sheet that has months across the top, and accounts down the side.

                             

                            If you were to drag the calc you created onto the rows shelf, Tableau would sum up the percentages that the calc creates.  But that's not what you realty want to see.  (You could have 100 transactions, each with 1% profit, and if you sum that up, you would get 100% profit, which, of course, would not be correct.)  And you can do an average of those 100 values because maybe one value is 99%, and it is your biggest transaction of the bunch, and it completely overshadows all the rest of the transactions, etc.  What you really need to do is get the sum of all the sales in that chunk of records, and get the sum of all the expenses, and re-do the math using those numbers.  So you would create a calc that looks like this:

                             

                            (SUM([Sales]) - SUM([Expenses])) / SUM([Expenses])

                             

                            This would be an aggregate calc.  It is acting across a bunch of rows and being directed into one cell on your sheet.  With that calc, Tableau would be taking all the sales that go into the cell for a given account for a given month, and all the expenses the same way, and doing the math on those values.

                             

                            When you drag that measure to the sheet, you'll see that Tableau does AGG([your aggregate calc]).  It doesn't do SUM() because it is already an aggregate inside the calc.

                             

                            And now I'll make a little side trip in this story.  Maybe you only want to do this calc for accounts where [Region] = "West"

                             

                            The first stab most people would make with this is the following:

                             

                            IF [Region] = "West" then (SUM([Sales]) - SUM([Expenses])) / SUM([Expenses]) END

                             

                            On the surface it looks good, you get an error about mixing aggregate and non-aggregate values.  When you have a function like SUM() or AVG(), the result of that clause is an aggregate.  But the field [Region] is not an aggregate.  It's a row-level value.  You get around that problem by telling Tableau to treat [Region] as an aggregate too:

                             

                            IF ATTR([Region]) = "West" then (SUM([Sales]) - SUM([Expenses])) / SUM([Expenses]) END

                             

                            In essence this is telling Tableau "Since I'm doing math at the aggregate level in this cell, just look at [Region] at the same level of evaluation.

                             

                            End of side trip.

                             

                            OK, so now you have a sheet with accounts down the side, and months across the top, and cells for each account for each month with a profit percentage by account and month.  YAY!  Next your customer say, "Cool, but what is my profit per month across all accounts?  And by your previous experience with calculating for each cell, you know you can't just add up all the percentages down the month column.  You want to get the sum of all the SUM(Sales) on the sheet.

                             

                            For this you want to do a TABLE calc.  This is a calc that acts across the table, not just on all the records that comprise an individual cell.

                             

                            To get the sum of all the SUM()s, do this:

                             

                            WINDOW_SUM(SUM([Sales]) - SUM([Expenses])) / WINDOW_SUM(SUM([Expenses]))

                             

                            (Note:  I could have put WINDOW_SUM individually on the two operands inside the numerator parentheses, but because addition/subtraction is associative (or is it distributive?  Algebra was so long ago...) the results are the same for that part.  But it was imperative that I did the divisor separately.)

                             

                            What this will do is add up all the expenses in the entire column, and all the sales, and then do the math accordingly.  It's a table calc because it's doing the operation across the table.  There is a whole different discussion we can have about telling the table calc what "direction" to go.  (Do it down the month column?  Do it across the account row?  Do it for the whole sheet?  Restart for every year, or every quarter?)  I won't get into that now.

                             

                            My point was to differentiate row-level calcs from aggregate calcs from table calcs.

                             

                             

                            And that's why I suggested you do some searching around to find more detail about all the different components.

                            • 12. Re: create calculated value based on other calculated values
                              Jean-thomas Meyer

                              Hi joe,

                              Thnaks a lot for your explanation it does make sense to me . I can see that it is not always a walk in the park, but I guess I have to practice.

                              To come back to you example with IF ATTR() = "West" then

                               

                              I guess that is excactly my issue, I have attached a new file in the top of the discussion and describe it better.

                              So what I would like to know is our to overcome my issue then, on which step should I do that ?

                              As is it is now I have 3 steps.

                               

                              1-    Create debit and credit as a calculated field : If  = “ Debit” then   and the same with “Credit”  and “penalties”

                               

                              2-    Now that I have my debit and credit I ll do some operation on them like adding  to each other, multiply with other , to create a couple of other calculated field

                               

                              3-    I make an aggregation based on level 2.

                              As you can see in the file I have attached it works find when I drill down, but doesn’t when aggregated.

                               

                              You can see it explain here : https://community.tableau.com/thread/207963

                               

                              Thanks again for you help

                              Jt

                               

                               

                              Med venlig hilsen

                              Jean-Thomas Meyer

                              • 13. Re: create calculated value based on other calculated values
                                Joe Oppelt

                                (Note to self:  Version 9.2)

                                 

                                See the attached.

                                 

                                If I understand your question correctly, when you expand your date field to hours, you get the correct numbers, but when you collapse it down to a date total, you get a huge total.  Your total just for the day of January 1 should be the sum of the totals for all the hours when it is expanded.  (I expanded it in Sheet 3.  It's just a copy of Sheet 1, but expanded.)

                                 

                                I added all the component fields that go into [DK1 DB] into tooltips.  Scroll over any bar to see the values.  I notice that your production price is always 21 on the hourly sheet, but way bigger on Sheet 1.  My guess is that it should also be 21 (in this case.)  The calc that is getting this number is doing a SUM.  When you have hourly rows, it's summing up all the rows that go into that one hour.  (Maybe you even have just one row per hour.)  Each bar on that sheet shows 21.  When you collapse that into a single day, all the rows that funnel into that one day are getting added up.  All those values of 21 are getting added up to arrive at 189.  My guess is that you do not want that.

                                 

                                I don't know your data well enough to know what your correction should be here.  Maybe do MIN() instead of SUM.  Or AVG.  And the same with Buy Price.  You will have to look at all these calcs to be sure they are actually doing what you want them to do.  Sometimes SUM is the right thing.  (Usually that's the case for volumes, for instance.)

                                • 14. Re: create calculated value based on other calculated values
                                  Jean-thomas Meyer

                                  Hi joe,

                                  You got it perfectly. But what I want is to see my profit as I can see on an hour level, to be seen on a daily level. So I want tableau to calculate base on each hour row and note based on grand total or sum, aveg, or any other aggregation .

                                  So as it is tableau is doing

                                   

                                  1                    unitprice         volume           unitcost          revenue

                                  Hour1             2                    3                    1                    =23-(31)=3

                                  Hour2             4                    10                  5                    =410-(105) =-10

                                   

                                  When I aggregate on day level I want to get 3-10 =-7 But tableau take all grand total and redo the calculation like this

                                   

                                                        6                    13                  6      then revenue = (613)-(136) = 0

                                   

                                  ☹☹☹ I just want to be able to keep the drill down possilities…. What is to be done ?

                                  Thanks again for your help.

                                  Jt

                                  1 2 Previous Next