IsNull() AND IsNull()

mikevbreugel — Wed, 25 Nov 2020 16:16:04 GMT

Hello Qlik Community,

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.

Thanks

Re: IsNull() AND IsNull()

Anil_Babu_Samineni — Wed, 29 May 2019 13:05:42 GMT

What it is returning when you use This condition as Filter?

Re: IsNull() AND IsNull()

zhadrakas — Wed, 29 May 2019 13:10:40 GMT

hey,
this calculated dimensions works for me
=IF(len(trim(End_Contract))=0 AND len(trim(End_Placement))=0,Today(),'')

Re: IsNull() AND IsNull()

mikevbreugel — Wed, 29 May 2019 16:58:00 GMT

Thanks for your answer.

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.

Re: IsNull() AND IsNull()

mikevbreugel — Wed, 29 May 2019 17:20:25 GMT

Hi Anil,

I'm thinking 'Which filter does he mean'. Do you mean a conditional Expression?

topic Re: IsNull() AND IsNull() in QlikView

IsNull() AND IsNull()

Re: IsNull() AND IsNull()

Re: IsNull() AND IsNull()

Re: IsNull() AND IsNull()

Re: IsNull() AND IsNull()