Forum Discussion

Jake_Raz's avatar
Jake_Raz
ETL
05-04-2023
Solved

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

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 co...
dashboards & reporting
Finance Analytics
  • Angelina_QBeeQ's avatar
    Angelina_QBeeQ
    05-05-2023

    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

Angelina_QBeeQ's avatar
Angelina_QBeeQ
ETL
05-05-2023

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