It's impossible to say without seeing an example of your data and data structure. Please post a Tableau Packaged Workbook.
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
done i have attached the doc in the original question thks
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.
done thks for your help
'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])
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
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.
+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.
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
Med venlig hilsen
Trading & Reporting
Tlf. +45 99 55 61 19
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.
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
Med venlig hilsen
(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.)
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.