Getting the Current Date and Last Week Date Record

Hi Alteryx Community,

I would like your help in obtaining records for the current six dates and the previous six dates. In my attached example, the current six dates range from June 27 to July 7, while the previous week's records span from June 26 to July 3. We will then calculate the total number of rejects for each category in both the current and previous records

I would also like to request a comparison of the current date between "paid off" and "no story execution found." If "no story execution found" exceeds "paid off," it should indicate that "No Story Execution found" is the leading reject. Otherwise, it should state that "Paid off is the leading reject." Additionally, please calculate the percentage change as illustrated below. Below are references to the concept.

Looking forward for your response. Thanks.

Kamen

Getting 6 days Current and Previous.xlsx

Workflow

Reporting

Accepted answers

AncientPandaman

@KamenRider you can use datetimeformat([Date],"%W") to get week number. where should be more accurate.
for Holidays, you can have a list then just join to exclude it.

from there you can just filter the weeknum and get last 6 count.

All comments

caltang

What have you done so far in your own workflow?

KamenRider

Hi @caltang

Thank you for your response. At this point, I'm unable to move forward because I'm having difficulty determining the current and previous six dates. I hope you can assist me with this.

Thanks,

Kamen

KamenRider

Hi Alteryx Community,

anyone who could help me on this?

Thanks,

Kamen

AncientPandaman

@KamenRider
you may start with crosstab by week number?

I believe you can use your current week data only since it also contains previous week data.

then you can continue onward.

KamenRider

HI @PangHC

I need to get the last 6 dates not counting the holidays and then the previous 6 dates. How am I going to do it?

Please show solution.

Thanks,

Kamen

AncientPandaman

@KamenRider you can use datetimeformat([Date],"%W") to get week number. where should be more accurate.
for Holidays, you can have a list then just join to exclude it.

from there you can just filter the weeknum and get last 6 count.

KamenRider

HI @PangHC ,

Already did that but it has missing date. While it count the 7/4 even though i dont have on my data, it only give me 5 dates. Sorry for that I accidentally click the solve solution.

Need another way to get the 6 dates. Please refer to my input file as reference.

Kamen

AncientPandaman

@KamenRider i thought it just need to filter by week number where using

# for current week
tonumber(datetimeformat(datetimetoday(),"%W")) = [WeekNum] 
# for previous week
(tonumber(datetimeformat(datetimetoday(),"%W"))-1)  = [WeekNum]

then just summarize it and get last six records from sample tool.

i suggest you to do more weekly challenge, because you need more practice to convert the action to steps in alteryx.

Quick Links

This months top contributors

hhaigh1 280

FláviaB 262

JORGE4900 235

caltang 177

DanM 167