Forum Discussion
calculate new table
Hi all,
Hope someone can help me with the following.
I have a table A:
| ctr_nr | date_in | order_date | date_out |
| A | 1-6-2022 | 4-6-2022 | 5-6-2022 |
| B | 2-6-2022 | 16-6-2022 | |
| C | 15-6-2022 | 23-6-2022 | |
| D | 20-6-2022 | 25-6-2022 | 26-6-2022 |
| E | 22-6-2022 |
I want to calculate a new table B, based on table A.
The logic is as follows:
From date_in, the value 1 is mentioned in table B;
When the order date is filled: from that date the value 0 is mentioned in table B instead of value 1;
From the date_out, the value 0 stops in table B;
After that we can calculate the sum for every date column
Table B:
| ctr_nr | 1-6-2022 | 2-6-2022 | 3-6-2022 | 4-6-2022 | 5-6-2022 | 6-6-2022 | 7-6-2022 | 8-6-2022 | 9-6-2022 | 10-6-2022 | 11-6-2022 | 12-6-2022 | 13-6-2022 | 14-6-2022 | 15-6-2022 | 16-6-2022 | 17-6-2022 | 18-6-2022 | 19-6-2022 | 20-6-2022 | 21-6-2022 | 22-6-2022 | 23-6-2022 | 24-6-2022 | 25-6-2022 | 26-6-2022 |
| A | 1 | 1 | 1 | 0 | 0 | |||||||||||||||||||||
| B | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | |
| C | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 0 | |||||||||||||||||
| D | 1 | 1 | 1 | 1 | 1 | 0 | 0 | |||||||||||||||||||
| E | 1 | 1 | 1 | 1 | 1 | |||||||||||||||||||||
| Total | 1 | 2 | 2 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 1 | 1 | 1 | 1 | 2 | 2 | 3 | 2 | 2 | 1 | 1 |
Thanks upfront for your advise!
John
Hi Anonymous ,
Believe this is possible using a measure however to create a new table you can do the following.
On the query editor follow the steps below:
- Reference table A
- Add a custom column with the following code:
try {Number.From([date_in])..Number.From([date_out])} otherwise {Number.From([date_in])..Number.From( Date.From( DateTime.LocalNow()) )}- Expand the new column
- Format the column has date
- Add a new column with the following code:
try if (if [order_date] = null then [Custom] >= [date_in] else [Custom] >= [date_in] and [Custom] < [order_date]) then 1 else if(if [date_out] = null then [Custom] >= [order_date] else [Custom] >= [order_date] and [Custom] < [date_out]) then 0 else null otherwise nullNow you have the new table:
2 Replies
- MFelix
Super User
Hi Anonymous ,
Believe this is possible using a measure however to create a new table you can do the following.
On the query editor follow the steps below:
- Reference table A
- Add a custom column with the following code:
try {Number.From([date_in])..Number.From([date_out])} otherwise {Number.From([date_in])..Number.From( Date.From( DateTime.LocalNow()) )}- Expand the new column
- Format the column has date
- Add a new column with the following code:
try if (if [order_date] = null then [Custom] >= [date_in] else [Custom] >= [date_in] and [Custom] < [order_date]) then 1 else if(if [date_out] = null then [Custom] >= [order_date] else [Custom] >= [order_date] and [Custom] < [date_out]) then 0 else null otherwise nullNow you have the new table: