Trailing 8-Weeks Historical Report

[vivaisun11]

Hello.

I am trying to create a report that sends our vendors an 8-week history of their on-time performance.

Our vendors sometimes do work on one week, but don't do work on another week.

For the weeks that I don't have any data, Alteryx completely eliminates the entire row.

How do I create a formula to show that they "didn't execute work" in a particular week, and still show 8 weeks history in the report?

Please see attached current view, and final preferred view.

[KGT]

Create another Input and Join to this with a Union to join the J with whichever of the L/R you need. So, the input could be a Generate Rows to generate the required lines, followed by a formula that makes the Week No. column.

You can of course, Insert text into a column etc before the union, if you want it to say "No work Entered".

Workflow:

Generate Rows:

[Qiu]

@vivaisun11 
How do you determine the start and end week of your 8-week histroy?

[vivaisun11]

Thanks, will test this out!

[vivaisun11]

The current week is the start week.

And I need 8 weeks minus current week. I used this formula for current week - "ToNumber(DateTimeFormat(DateTimeToday(),'%U'))+1"

[vivaisun11]

@KGT Thanks. I'm partially there - I'm able to generate the rows for the 8 weeks using the MIN and MAX.

I have other vendors data in the same source dataset where the other vendors DID execute on the weeks that some vendors haven't. So when I join the newly generated rows, it is joining with rows that already exist in the data, instead of the missing weeks.

Not sure if I was able to explain that clearly.

[KGT]

Not sure how you identify the other vendors, but don't think to **bleep** this one, as you may rabbit hole. You just need to also join on the vendor field. So in the formula, create a field for the vendor and join that to the vendor field on the other side. How dynamic you make that depends on what else the data looks like and how you want to present it otherwise. The other option is filtering to just this vendor before the join... either way will work, just depends on what's happening after that.