I am very new to tableau so please excuse me if the answer is really simple.
I have a field called Units Shipped and Another called Quantity Allocated. Now when Units Shipped= Quantity Allocated a calculation shows open or closed.
In a separate place, I need the count of total open orders. I am trying
count(IF [Units Shipped] = 0 THEN 1
ELSEIF
SUM([National Allocation].[Quantity Allocated(State wise)])-(SUM([Batch Details (Batch Details)].[Units per Batch]))>0
THEN 1
ELSE 0
END)
the first error I get is count is an aggregate function and can not be aggregated further. when I remove count, this keeps giving me answer as 1 which is incorrect.
Please help!
OK. I think easiest if I push something to Tableau Public for you to download. https://public.tableau.com/views/ForSwarnika/SummaryData?:language=en&:display_count=y&:origin=viz_share_link
To summarise the steps:
1. Blend on Allocation Number - Edit Relationships under the Data menu
2. Create a field [Is Open Allocation]:
IF [Units Shipped] = 0 THEN 1
ELSEIF
SUM([National Allocation].[Quantity Allocated(State wise)])-SUM([Batch Details (Batch Details)].[Units per Batch])>0
THEN 1
END
3. Allocation Number needs to be on the Detail shelf. It won't work without that field in the view.
4. Because Allocation Number needs to be in the view we have to Window_sum the [Is Open Allocation] field
WINDOW_SUM([Is Open Allocation])
5. The Compute Using is by Allocation Number
6. It will return the same number multiple times for each state. Therefore create a new calculated field to only return the first number for each state:
FIRST()=0
7. Set that to also Compute Using Allocation Number
8. Put that fields to Filters and set to True.
