Unable to apply datediff(d1,d2) if value flag in col1=1

Question

In quicksight I want to calculate the Datediff only in the rows where value flag for cancellation=1(True) and add the output in new column on same row level, for row where cancellation=0, do not perform datediff

My Quicksight query - this isn't working - Need Help with this ifelse(cancellation='1',dateDiff({upload_dt},{billed_date}),'null')

My excel query - THIS IS WORKING =IF(D2=1,(B2-C2),"null")

Answer 1

In the date diff function you're not specifying the date granularity. For years do ifelse(cancellation='1',dateDiff({upload_dt},{billed_date},'YYYY'),'null') For days do ifelse(cancellation='1',dateDiff({upload_dt},{billed_date},'DD'),'null')

More information is available here: https://docs.aws.amazon.com/quicksight/latest/user/dateDiff-function.html