To put it simply: Is there an easy way to count the number of consecutive weeks that a customer has an order over a 4-week span?
Hi @durl,
hard to tell without knowing the data structure.
Can we assume you have “date”, “order”, “customer”?
How to you define the 4 week span? What’s start of the week, dynamic or fix?
BR
Yes. I have a ticket date (which is translated into a fiscal week), ticket number, and customer.
So, over the last four fiscal weeks, how many fiscal weeks are there a ticket for the store? I would like to have those four weeks be dynamic–the last four fiscal weeks including the current one.
Hello @durl, there would be 2 ways to go about this. If you want to base it off a parameter value that is being set by the user, you can use that value to check this, otherwise, if you want to base it off of the current week, we can use now(). It would look something like this:
You can use this to return data in the 4 weeks you are checking. I am adding truncDate around the date fields to ensure they are properly checking for the week value, and if you want it to be dynamic with the current time, replace ${Parameter} with now():
last4 = ifelse(truncDate('WK', ${Parameter}) >= truncDate('WK', {ticket_date}) AND addDateTime(-4, 'WK', truncDate('WK', ${Parameter})) <= truncDate('WK', {ticket_date}), {ticket_date}, NULL)
Then, you can do a distinct count of the ticket numbers for each week of each store using the last4 field:
ticketsPerStore = distinctCountOver({last4}, [{store}, truncDate('WK', {ticket_date})], PRE_AGG)
Now that you know the number of tickets per week and per store, you can add a final calculated field to check if the value is greater than 0 to determine if your metric is true. This should lead you towards are desired solution. If you have any follow-up questions, please let me know!