Forum Discussion
Help please, guidance required
Good day all,
Below I have a simple table of data something like the below example, the CO number ala the customer order is the number our system gives to each customer order, now the delivery number is the number used to deliver the stock to the customer. Its always liked the CO number but it's not always a 1 to 1 ratio as per the below example. One CO number but two delivery numbers as the stock was delivered in two stages.
What I'm trying to measure is the following,
1. How many perfect customer orders have we delivered, an example is a 2end order 1005552 its perfect order, 1 co 1 delivery number and all delivered in full.
2. I would like to flag every bad order aka order 1005551 and then work out how many days it takes from start to finish, in this case, it's taken 5 days to deliver the complete order.
| Customer Order | Delivery Number | Customer Requested Date | Departure Date | Item | Order qty | delivered qty |
| 1005551 | 5551 | 15/11/19 | 16/11/19 | a1 | 2 | 2 |
| 1005551 | 5552 | 15/11/19 | 20/11/19 | b2 | 1 | 1 |
| 1005552 | 6661 | 20/11/19 | 20/11/19 | a1 | 2 | 2 |
Thank you all and I look forward to your answers to my questions 🙂
Regards
Snowy
Please follow the below steps.
1. Right click on table (Ex:- DOP-055 1)
2. Click on "Duplicate", you will get another table. Remove unnecessary columns and keep required columns. (Ex:- ONo,DelNo,CusReqDt,DepDt,Item,ItemOrderQty,ItemDelQty).
3. Clcik on "Group By" based on columns. (Ex:- Ono - only one i used here, based on your requirement you can use the columns here).
4. Select the "Original Table (Ex:- DOP-055 1)" and click on "Merge Queries" and select the common columns and clikc ok.
5. Expand the column and select required columns and change the column by double click on it (Ex:- Cnt).
6. Click "Close and Apply".
7. Now you can able see the "Cnt" column in Main Table and Click on "New Column" and assign it as "Flag" and write a query like below.
Flag = IF(Snowy34[Cnt]=1,"True","False")8. Based on your requirement, if the Flag have true, it is nothing but "perfect order".Hope it will help for your requirements.Please refer the link for the snippets.If you have any queries, please let us know.
5 Replies
Please follow the below steps.
1. Right click on table (Ex:- DOP-055 1)
2. Click on "Duplicate", you will get another table. Remove unnecessary columns and keep required columns. (Ex:- ONo,DelNo,CusReqDt,DepDt,Item,ItemOrderQty,ItemDelQty).
3. Clcik on "Group By" based on columns. (Ex:- Ono - only one i used here, based on your requirement you can use the columns here).
4. Select the "Original Table (Ex:- DOP-055 1)" and click on "Merge Queries" and select the common columns and clikc ok.
5. Expand the column and select required columns and change the column by double click on it (Ex:- Cnt).
6. Click "Close and Apply".
7. Now you can able see the "Cnt" column in Main Table and Click on "New Column" and assign it as "Flag" and write a query like below.
Flag = IF(Snowy34[Cnt]=1,"True","False")8. Based on your requirement, if the Flag have true, it is nothing but "perfect order".Hope it will help for your requirements.Please refer the link for the snippets.If you have any queries, please let us know.I'm assuming that is your raw data but if it's not i'll make two scenarios:
- Raw table : calculated column with a simple if statement like 'Perfect order = if [order qty]=1 and [delivered qty]=1 then true else falseFrom here you can pass a visualization filtering by that column results.
If thats a visualization alread you can make the same but in a measure with the same logic.
BR,
DR
Good day Anonymous
I have tried that but in my case below with the table only one order is a perfect order and that is 1005552. The IF statement gets me only so far it still doesn't provide me with a true answer what order is a PERFECT order, a perfect order must be delivered in full and must be on 1 CO number.
Regards
