I've made a Pivot Table that contains 2 start dates and 2 end dates. 1 start and end date for the placement of a person at a company and 1 start and end date for the contracts that are in the placement. There can be more contracts in 1 placement. These are of course dimensions. The expression sums the booked working hours and shows them on a weekly basis and it shows a specific year. Lets say for our example it's 2019.
Person ID
Start Placement
End Placement
Start Contract
End Contract
1
2
3
4
5
1
1-1-2016
-
1-1-2016
7-1-2019
40
-
-
-
-
1
1-1-2016
-
7-1-2019
-
-
40
40
40
40
2
1-1-2018
14-1-2019
1-1-2018
-
24
24
-
-
-
I'm trying to calculate how many hours I expect to be booked per row to find the differences between the calculated hours and the booked hours. For that I need to make a new Start date and a new End date. Sometimes it needs to be the End date of the placement and sometimes it's the End date of the contract, but it is also possible that both placement and contract end date are NULL.
The problem that I encountered is when I make a calculated dimension to make a new end date is that when both values are NULL it doesn't return a value at all.
=IF(IsNull(EndDatePlacement) AND IsNull(EndDateContract),Today(),'')
I know I need to do more than just the above calculation to get where I need to be concerning my question and problem, but first I need to fix this problem.
I tried them both without the other EndDate (no AND) and it finds both the values when they are NULL. Just not when I try it with a AND statement. Also tried the Len()=0 method but it didn't work as well.
Is there someone that can help me with this problem.
What it is returning when you use This condition as Filter?
Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
I've tried this because I didn't try it with Trim, but it does the same and gives me no value (-). When I try only one or the other without the AND statement IF(Len(Trim(Contract)=0,Today(),''), than it does return the date.
