I have a Data Set that contains records about order numbers and their statuses. “Order number” and “status” fields are not unique. The primary key of the table is a combination of order_number and status. “Completed date” is another field that is null/includes date depending on whether the order was completed.
For the same order number, there may be more than one status, so there will be more then 1 records. As an example, order number 1 could have two records, one with “created” status and one with “finished” status . It is possible to tell whether an order is complete or not by checking if there is a “completed date” under one of her records. If it’s not null, then the order is complete. However, it’s not a requirement that all of the order records show the completed date. As an example:
The orders 1, 2, and 4 in this dataset are closed, and the order 3 is open. I created a calculated field that gives 1 if the completed date is present, and 0 if it’s not:
Afterwards, I check if the sum of closing_code >0 for any order number, if it does - the order is close, otherwise, it’s open.
I don’t know why the calculated field “close_or_open” doesn’t work, and it shows as “unavailable”. Does anyone know why it looks like that?
Hello @Noys - Thank you for posting your query. When you say that the close_or_open calculated field is showing you “Unavailable”, is it at the dataset preview? If yes, then that’s an expected behavior. Dataset Preview can’t show that field value as it involves level aware calculation (LAC) which seeks for active context. Once you use the field in a visual, you should see it showing proper value according to the active context. Hope this helps! Let me know otherwise. Thank you!
Did my suggestion help you in resolving your query? If yes, would request you to mark the post as “Solution”. This will help the community to find guidance and answers to similar question. Thank you!
Actually, it wasn’t in the dataset preview, but in the analysis. I tried a few times, and finally it worked and classified my orders as “Closed” or “Open”. However, when I try to apply the filler so that the dashboard will only show open orders, it doesn’t work.
@Noys - Thanks for your response. Unfortunately, I could not understand the problem. If you can share some more details with example/snapshots that would help me to understand it better. Thank you!
