I have a request to have a report that shows the number of calls made on an account that happened the day prior. My issue is when they load the report on Monday my data is showing Sunday, which is always showing no data since we don't work on the weekend.
I have a data source where it lists all the calls made to accounts over the last 3 days, what I need is for the value I use to lookup yesterday's date to be 3 days prior when the current day is a Monday.
Is there anyway I can do this?
Hi Sean,
In your scenario, you'd want Monday's calls to be calculated as Friday, but Tuesday-Friday, be calculated just on the previous day? That should be possible because some of our date functions allow you to skip weekends. However, if your dates are very straightforward, you could do an IF with DATE_ADD as well
The actual format will depend on what your dates currently look like. You could do an
newDate = IF(Monday, DATE_ADD(<date>,day,-3),DATE_ADD(<date>,day,-1)
If you'd like specific help with your Klip, please reach out to support and let us know where to view your formula in construction.
Thank you
Meggan
I guess my follow up is how do I let Klipfolio know it is Monday? I have 2 datasources, one that is yesterday for every day that isn't Monday and another I want to use on Monday, but I don't know how to do the 'Monday' you listed in example.
Hi Sean,
Can you give an example of your date format? You can convert a date to be day of week using EEE or EEEE.
https://support.klipfolio.com/hc/en-us/articles/216182377-How-do-I-use-Date-Time-formats
For example: DATEVALUE(yourDate,"EEEE") would return Monday - assuming yourDate was in unix format.
Thanks - Meggan