Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Conditional Formatting on Date Range

Nageen
8 - Asteroid

Hello! I'm trying to add conditional formatting to a weekly report to ensure comments are addressed within two weeks. I have attached a sample typical weekly report. For each unique ID, I want to calculate the difference between the last modified date for that ID and the first creation date for that ID. If it's >14 days, I'd like the cell to turn red, if not, it can stay white or turn green. 

 

Adding a new column "Within SLA" with a "yes" (<14 days) or "no" (>14 days) would also work.

 

Thank you!

7 REPLIES 7
Nageen
8 - Asteroid

example report attached 

CarlDi
Alteryx
Alteryx

Hi @Nageen,

 

I would worry about conditional formatting once the data has been output to excel or another consumption layer. For your alternative ask, I've attached a workflow. In summary, simply change the two fields with datetimes to a DateTime format (because it's a CSV, alteryx will read them in as strings). Then with the summarize tool, group by the ID and select "First" for the Create Time field then "Last" in the Modified Time field. Then with the formula tool, use thedatetimediff expression to bring back days as the unit. Then finish it with the conditional statement. Hope this helps!

Loic
Alteryx
Alteryx

Date-SLA-example.PNG

Your example of date didn't contain any ID that was > 14 so I changed the month for the first ID for one of the dates so the difference would be > 14.

Nageen
8 - Asteroid

Worked perfectly, thank you so much!!

Nageen
8 - Asteroid

Hi @CarlDi 

 

One more question: I now want to only calculate the "Days Difference" if the comment has been marked as "Resolved" in the "State" column. I tried altering the original formula, but I'm getting an error due to the data type being a string rather than integer.

 

 

if [State] = 'RESOLVED' then DateTimeDiff([Last_Modify Date],[First_Create Date],'days') else 'N/A: Open Comment' endif

 

Do you have any ideas on how I could solve this error? 

 

Thank you so much! 

CarlDi
Alteryx
Alteryx

Hey @Nageen - i added a separate formula tool to my original attachment. I got it to work, although keep in mind, our field names aren't identical.

 

Hope this helps!

Nageen
8 - Asteroid

Thanks so much! 

Labels