Count days between Open Date and Closed Date, but use today's date if there is no Closed Date

[Jake_Raz]

Hello! I'm trying to create a formula that will calculate the number of days between two dates. The first date would always be an "Open Date", which all items should have. The second date would be conditional: I want it to use a "Closed Date", or if there isn't a Closed Date (i.e. it's null), then use today's date.

I tried using this formula:

IF(ISNULL([Days in Dispute Closed On]),
DDiff(Now([Days in Dispute Opened On]),[Days in Dispute Opened On]),
DDiff([Days in Dispute Closed On],[Days in Dispute Opened On]))

Basically: if Closed On is null, calculate difference between Today and Opened On, otherwise calculate difference between Closed On and Opened On. Seems pretty straightforward, but Sisense won't accept this. It says "Error in function definition (IsNull): Expecting parameter of type 'Numeric Expression' but found 'Set'."

I tried changing the ISNULL so it was looking at a count instead of an actual date:

IF(ISNULL([# of unique Days in Dispute Closed On]),
DDiff(Now([Days in Dispute Opened On]),[Days in Dispute Opened On]),
DDiff([Days in Dispute Closed On],[Days in Dispute Opened On]))

But that just changes it to a different error: "Error in function definition (IF) in 'numeric expression 1 (true)': Expecting parameter of type 'Member Expression' but found 'Dimension Expression'."

This doesn't make any sense, because if I just do this by itself:

DDiff(Now([Days in Dispute Opened On]),[Days in Dispute Opened On])

it works just fine?? So why wouldn't it work as part of the IF statement?

What am I doing wrong? How can I accomplish the calculation that I want?

[Angelina_QBeeQ · Accepted solution]

Hi @Jake_Raz ,
The DDIFF() function doesn't work as expected in conditional expressions now. If possible, you can make a conditional field at the cube level. Or you can try this formula:

case 
when [# of unique Days in Dispute Closed On]=0 
then min(DDIFF(Now([Days in Dispute Opened On]),[Days in Dispute Opened On])) 
else min(DDIFF([Days in Dispute Closed On],[Days in Dispute Opened On])) 
end

Hope it will help you.

Always here to help,
Angelina from QBeeQ
[email protected]
QBeeQ  - Gold Implementation and Development Partner