Forum Discussion

Regular Visitor

6 years ago

Solved

Issues when evaluating records for three conditions, include a date element

I've got a data set showing equipment serial number and a variety of statuses as a user works to update the software. Starts with consent, then download pending, download started, download finishes a...

Anonymous

Not applicable

6 years ago

Here's the right solution:

Download_Expire =
var __currentStatus = MV_RT_FL_MONTHLY[Flash Status Description]
var __serialNumber = MV_RT_FL_MONTHLY[EQUIPMENT_SERIAL_NUMBER]
var __currentDate = MV_RT_FL_MONTHLY[Flash_timestamp]
return
IF (
    __currentStatus = "DOWNLOAD_FAILED"
    &&
    NOT ISEMPTY(
        FILTER (
            MV_RT_FL_MONTHLY,
            MV_RT_FL_MONTHLY[EQUIPMENT_SERIAL_NUMBER] = __serialNumber
            &&
            MV_RT_FL_MONTHLY[Flash Status Description] = "DOWNLOAD_PENDING"
            && 
            MV_RT_FL_MONTHLY[FLASH_TIMESTAMP] = __currentDate - 7
        )
    ),
    
    "DOWNLOAD EXPIRED"
)

Best

ttoastt
Regular Visitor
6 years ago
I tried this and it's returning blanks, so I removed the date condition within the filter and now it does return 'download_expired' for every row that has a status of 'download_failed'. It looks like the date comparison is what is causing issues.
- Anonymous
  Not applicable
  6 years ago
  If timestamp is a datetime and not a date, then it certainly causes issues. If you have a datetime, you have to do it in a different way. You have to have a column with just dates, no time involved.
  
  Then the formula will work.
  
  Best
  D
  - ttoastt
    Regular Visitor
    6 years ago
    Thank you, this set me down the correct path. Retyped it and was prompted to specify .[Date] after the date column and that is yielding the desired output. Thank you!