Best Of

Re: Release Notes - What Changed

Thanks @blool1 but that just highlights the new features that are coming out. It doesn't get into technical details about any of the underlying data changing - case in point there's a new activity log type called workbench_JOB which is now different than the old JOB type. When we've configured our data flows to look for the JOB for workbench jobs that's no longer the case causing the DataFlow to silently "fail" (it runs but the data is no longer correct). I'm looking for more detailed technical changes that would affect the administration of my instance.

GrantSmith

4

Re: Beast Mode for Aging Bucket

Your days are negative because your date assigned is coming after the application started date. And because -27 is less than 15 they're all falling into the bucket. You have a few options. One is you can utilize the ABS function to convert any negatives to positive so in you cases where the difference is -27 it'll evaluate to 27 and fill the bucket.

(CASE WHEN ABS((DATEDIFF(CURRENT_DATE(), `Application Started`)) - (DATEDIFF(CURRENT_DATE(), `Date Assigned`))) <= 15 THEN '15 Days'

 WHEN ABS((DATEDIFF(CURRENT_DATE(), `Application Started`)) - (DATEDIFF(CURRENT_DATE(), `Date Assigned`))) > 15 AND ABS((DATEDIFF(CURRENT_DATE(), `Application Started`)) - (DATEDIFF(CURRENT_DATE(), `Date Assigned`))) <= 30 THEN '15-30 Days'

 WHEN `Application Started` is NULL THEN 'App Not Submitted' END)

GrantSmith

2

Re: What's wrong with this case when statement / how do case when's operate on the back end?

Thanks!

user049141

1

Re: What's wrong with this case when statement / how do case when's operate on the back end?

The issue is because your function is only going to return the week number variable when work date equals today. You can do this much simpler by using this as your beast mode:

case when `Week Number` = WEEKOFYEAR(CURRENT_DATE()) then 'Current Week'
else 'Not Current Week' end

You could also choose to use your workdate field instead of your weeknumber field by changing your beast mode to this:

case when WEEKOFYEAR(`WorkDate`) = WEEKOFYEAR(CURRENT_DATE()) then 'Current Week'
else 'Not Current Week' end

You can use whichever is easier for you to read.

MarkSnodgrass

2

Re: Rename filter values to "Other"

Hi @user000253

You can use a beast mode to create your custom buckets (Name 1 & Other) and use that as your quick filter.

CASE WHEN `name` = 'Name 1' THEN `name` ELSE 'Other' END

This is assuming your column is called 'name'

GrantSmith

2

Re: Where I can find the "Saved post" metric in Facebook Advanced Ads Connector.

Thanks for your answer! I've got the metrics!

nbrx

1

Re: Activation Time <10

Ah the reason I'm not a fan of TIMEDIFF. In cases like this I prefer to use UNIX_TIMESTAMP and math to calculate the difference in seconds between two different timestamps.

SUM((CASE

  WHEN ((UNIX_TIMESTAMP(`Start Time (UTC)`) - UNIX_TIMESTAMP(`Arrival Time (UTC)`)) / 60) <=10 THEN 1

  END))

  /

  SUM((CASE WHEN `Case Number` is not null then 1

  end))

One question of clarification - If a start time comes 15 minutes before the arrival time (-15 minutes) should that be included since -15 < 10 or should it be excluded since the absolute duration is greater than 10? If it should be excluded you can wrap the subtraction in the ABS function to get the absolute value.

GrantSmith

2