see the attached approach
it returns this for purple
and this for all
I entered your data and pivoted it to get it into a form that works well with database driven software like tableau
color of product.twbx 22.9 KB
The data isn't coming from Excel. The real data is coming from a db.
I must have missed that in your post - i will get back to you
please see the attached approach
I duplicated the ssource
and also this
in summary it returns this
or the detail like this
If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution. Thank you.
Based on your excel,
From your database use the following query in the custom SQL and the follow Jim Dehner's answer
Select Product_1 as Product_value,Date,Product_count,'Product_1' as Product from table
Select Product_2 as Product_value,Date,Product_count,'Product_2' as Product from table
Select Product_2 as Product_value,Date,Product_count,'Product_3' as Product from table
This will pivot your data into the following format (Pivoting first 5 rows from your sample)
This can then be used in your dashboard as Jim showed
Hope This Helps
I was on vacation so I am working on this now. From what I can tell it does give me the format I need but it seems the numbers don't add up. If I do a simple query against the database using sum(product count) where product = product1 and product value = blue for instance the numbers are different then what I see in the tableau dashboard.
I'll keep investigating and get back to you.
After looking at everything it seems to me like what I asked to do will not work this way. I think I will have to have each row(combination of product1,2,3) as a group/product and sum by those. If you think about it you can't group by for instance Product 1 blue because the count isn't broken down by color and product but is a result of that combination of products and colors.
Can you duplicate and union Database connections?