-
1. Re: Getting total sales in tableau for all order numbers that have order number same as beverages
Adam Crahen Apr 27, 2016 6:41 PM (in response to Shivang Desai)Hi Shivang-
I created two calcs to do this. You can filter on this calc, I just left it in there for you to see how it worked.
Beverage Isolation
IF [Category] = "Beverages" THEN 1 ELSE 2 END
Order Contain Beverages?
IF {fixed [Sales Order] : MIN([Beverage Isolation])} = 1 THEN "Yes" ELSE "No" END
9.3 attached.
-
Bev.twbx 29.2 KB
-
-
2. Re: Getting total sales in tableau for all order numbers that have order number same as beverages
Shivang Desai Apr 27, 2016 10:19 PM (in response to Adam Crahen)When I create second calculation it says "bad character" (due to curly braces and colon I believe)
-
3. Re: Getting total sales in tableau for all order numbers that have order number same as beverages
Tharashasank Davuluru Apr 27, 2016 10:32 PM (in response to Shivang Desai)Hi shivang desai,
which version of tableau desktop you are using. The second calculation was done by using LOD expressions which will be supported by the (>x versions. Please make sure that you are using the 9.0 or higher versions.
-
4. Re: Getting total sales in tableau for all order numbers that have order number same as beverages
Shivang Desai Apr 27, 2016 10:39 PM (in response to Tharashasank Davuluru)Hi,
I have Tableau 8.2 version. Now how can I solve this?
-
5. Re: Getting total sales in tableau for all order numbers that have order number same as beverages
Tharashasank Davuluru Apr 27, 2016 10:44 PM (in response to Shivang Desai)Hi Shivang desai,
You are using lower version than 9.0 . So, LODs are not supported in that 8.2 version. if you want to use LOD then you need to upgrade your TD to 9.0 or higher version or else you need to write the calculations manually by breaking down them it into several parts.
-
6. Re: Getting total sales in tableau for all order numbers that have order number same as beverages
Shivang Desai Apr 27, 2016 10:48 PM (in response to Tharashasank Davuluru)Any idea on doing the above?
-
7. Re: Getting total sales in tableau for all order numbers that have order number same as beverages
Ashish Chaudhari Apr 27, 2016 11:46 PM (in response to Shivang Desai)Hi Shivang
I tried to calculate the things the way you have want but I am stuck at an issue, which is my calculations are giving the proper order ID which contains the beverages. When I use them it only considers the Beverage and ignores the second item from the order. I think it need to be achieved using some restructuring of data.
Guys Please take it forward from here if possible.
Attaching the 9.2 (Tried achieving without LOD)
Truly need some expert advice/guidance. Mahfooj, can this be achieved without using the LOD?
Thanks and Regards,
Ashish Chaudhari
-
food.twbx 21.1 KB
-
-
8. Re: Getting total sales in tableau for all order numbers that have order number same as beverages
Tableau kumar Apr 27, 2016 11:49 PM (in response to Shivang Desai)We have to biuld "BRIDGE TABLE" at DB level to implement the N: N Relationship. (Namy to Many Relationship between 2 Tables).
-
9. Re: Getting total sales in tableau for all order numbers that have order number same as beverages
Ashish Chaudhari Apr 27, 2016 11:49 PM (in response to Adam Crahen)Hi Adam,
is there any workaround for this in tableau 8.2 version?
I am sure this can be possible without using LOD functions. (tableau must have considered this scenarios before releasing 8.2 version)
Thanks and Regards,
Ashish Chaudhari
-
10. Re: Getting total sales in tableau for all order numbers that have order number same as beverages
Ashish Chaudhari Apr 27, 2016 11:52 PM (in response to Tableau kumar)Hi Kumar,
I think your suggestion is right. Can you try to demonstrate it? Even I had a say that there is some data restructuring or something at the data level needs to implement which will consider multiple items for one order.
Thanks and Regards,
Ashish Chaudhari
-
11. Re: Getting total sales in tableau for all order numbers that have order number same as beverages
Shivang Desai Apr 27, 2016 11:58 PM (in response to Ashish Chaudhari)Do you mean to say that need to restructure the database? Or it can be done directly through Tableau?
-
12. Re: Getting total sales in tableau for all order numbers that have order number same as beverages
Tableau kumar May 4, 2016 2:24 AM (in response to Shivang Desai)Books_Table
Book Author
B1 A1
B1 A2
B1 A3
B2 A1
B3 A2
B4 A3
Sales_Table
T_ID Book Sales
T1 B1 100
T2 B2 100
T3 B3 100
T4 B4 100
As per the SalesTable, Total Sales Volume is 400.
But when we connect these two Tables using Books_Table.Book= Sales_Table.Book
Author Sales
A1 200
A2 200
A3 200
Here we get the Total Sales as 600, but as we known there is Total Sales AMT is 400.
Here 1 Book (B1) is authored by 3 Authors, so B1 sales AMT is reflecting to 3 Authors.
We can say 1 Book can be written by multiple Authors and 1 Author can write multiple Books. (Many to Many).
Here we have to create a NEW BRIDGE TABLE @ DB Level.
A bridge table
sits between a fact table and a dimension table and is used to resolve
many-to-many relationships between a fact and a dimension. Bridge
table will contain only two dimension column ,key columns in both dimension
.Suppose there are two dimensions Customer
and Region,create table
Customer(Customer_id
int, Name varchar(110));create table
Region(Country_id
int, Desc varchar(50));Bridge
table is created by joining the two tables Customer and Region using dimension
keyscreate table Brige_table_Custregion (Customer_id int, Country_id int)
Best Regards
Kumar
-
13. Re: Getting total sales in tableau for all order numbers that have order number same as beverages
Ashish Chaudhari Apr 28, 2016 12:44 AM (in response to Tableau kumar)Trying this out.
Thanks Kumar.
-
14. Re: Getting total sales in tableau for all order numbers that have order number same as beverages
Tharashasank Davuluru Apr 28, 2016 12:58 AM (in response to Shivang Desai)1 of 1 people found this helpful