Creating Keys based on customer IDs and 30 Day Peroids

[JordyMicheal]

Hi All,

Have an interesting use case:

I want to group and make keys for customers (attached is the data I am using as an example)

I want to take a customer and make a key for them (IE starting at 1 and the "second key" would be 1)

The second time that customer has a line WITHIN 30 DAYS, the key would be 1 and a second key of 2
If another occurrence of this happened WITHIN 30 DAYS, the key would be 1 and the second key would be 3.

If the occurrence then happened outside of 30 days, they first key would go to 2 and the second key would go to 1 (ie 2.1) making a tree.

When it hits 112 customer, then I want it to go back to 1.1 for a new customer. I hope that makes sense......

TLDR; Making keys for users based on 30 day periods of last tickets 🙂

[NicoleJohnson · Accepted solution]

Multi-Row tool is going to be your friend on this one... could probably be done more efficiently, but this is at least pretty transparent. Take a look at the attached example - hope that helps point you in the right direction!

Cheers,

NJ

[JordyMicheal]

I love it.

I went with the same start, but missed the FLOOR in the 3rd.

Thanks Nicole! A true wizard as always

[Whodathunkit]

Thanks @JordyMicheal and @NicoleJohnson!

I'm just dissecting the second Multi Row Formula, trying to understand what's going on. Sorry if all of this is quite basic, but I'm fairly new to this.

For Else Floor([Row-1:Key1])+1, what does "Floor" do in this instance? I thought floor was just used for rounding and it doesn't seem like there's a need to round anything here.

Also, in the same expression, Elseif [LessThan30] Then [Row-1:Key1], is there nothing needed after the [LessThan30] as it's a True/False statement? If nothing is stated, is it implied to be True?

[NicoleJohnson]

Oops!! That Floor was totally a leftover from the first method I used, where Key 1 & 2 were combined (so 1.1 in a single field instead of 1 and 1 in two fields). Forgot to take out the Floor portion when I went to the 2 keys method. 🙂 Nice catch!

You could use Else [Row-1:Key1] + 1 and it would work just fine for the second Multi Row Formula... as for your question about True/False, I set up the [LessThan30] field to be boolean type (True/False) so there is nothing else needed beyond the field itself to evaluate whether it is True or False. If I had set it up as a string or numeric field data type instead, I would have to modify the expression to be something like Elseif [LessThan30] = 1 Then [Row-1:Key1].

Hope that helps, and thank you for asking these questions to help with clarification!!

NJ

[Whodathunkit]

That helps a ton and makes perfect sense. Thank you for the explanation on both points. The workflow is working great and has made the hours I spent earlier on this completely unnecessary lol.

Thanks again!

Tyler