Thank you for your reply John!
The problem is that I have only access to Tableau Server (not directly to conventional DB). On this server I cannot do any custom SQL queries, nor pivot the table. That is why I was wondering wheather it is possible to do the calculation using calculated field.
Rytis, Sorry I missed that part and just thought it was a database. I am not sure that is possible via calculations. My guess is no but maybe someone else can say for sure.
Your actual case may be a bit more complicated than your example, in which case this proposed solution might not work as well as you'd like, but one possibility is to create a separate data source (in an Excel file, for example) which contains a combination of all the dates and all the resources. Then, you could use that as the primary data source to get a view for the date and resource and then blend (3 times, to 3 copies of the data source above, for each of the Resource columns) to get the total values for each kind of resource for each date.
There are a few things that would make this solution less attractive:
- If there are additional dimensions (other than date and resource) that need to be used (as filters or slicers)
- If there are more than 3 resource columns (you'll have a copy of the Tableau data source for each additional column, so that gets messy rather quickly)
- If you have some views based on the original data source and some based on this new primary data source and you want to use them together in a dashboard with shared filters (though Tableau 10 will make this a bit easier with cross data source filters)
A tip on the new data source: if you have one Excel tab with all dates and a Join column that always has a single constant value (e.g. 1) and another tab with all the resources a Join column with the same value, you can create a data source in Tableau that joins the tabs to easily give you the combination of all dates and resources (it's effectively a cross join)'
Thanks for reply Joshua,
I had to put my example in excel and change names for security reasons.
The main problem is that the client has provided us the data in wide format. We access our data only through dedicated Tableau server.
They ask us to see total of each Reasource by date (Monthly, quarterly, etc.). I have consulted my collegues but they could not find any solutions. That is why I am here.
We could ask the client to give us a new table but it is not very efficient since volumes are huge (hundreds of milions of rows). Ideally we could use the same table (like in the example) to generate resource usage reoprt.
I still hope that the Tableau Community will be able to help or maybe more people would say that it is impossible and I could try to find solution outside tableau.
I understand. My suggestion was not to change the source you have (knowing that you are unable to change it), but rather to supplement it with another source that you can control and blend the two together. Is that a possibility?