Ranking based on various time periods & a specific dimension

[udles5666]

Hi Community 

I need help with the problem I am trying to solve. I had a crack at it but wasn't able to get my head around. Can someone please help? Attached package & desired output.

Step 1 is to calculate what is year, quarter & 4 week sales of a store based on their specific State & Regions. 

Step 2 is to calculate the rank for Year, Quarter & 4 Weeks at get the time period at a row level.

Final output columns below :

Please see screen shot attached with the post.

Thanks,

[Qiu]

@udles5666 

This is not one of the Alteryx certificate questions, right?

[udles5666]

100% it's not. @Qiu  :) 

[Qiu]

@udles5666 

Thanks.
Let me ask again, sorry

1. For Sales of Yea, it is from Jan1 to Dec. 31?

2. Quater stars from when?

3. 4 Weeks starts from when? 

[udles5666]

@Qiu - No worries at all mate. 

Max Order Date can be the Anchor Date, meaning: 

1. Year Time Period will be - 52 weeks from the Anchor Date (Max Order Date) 

2. Quarter Time Period will be - 13 weeks from the Anchor Date (Max Order Date) 

3. 4 Weeks Time Period will be - 4 weeks from the Anchor Date (Max Order Date) 

So if the the maximum order date is 31st Jan 2022, Year calculation will be minus 52 weeks from 31st Jan 2022. 

Does this make sense? Thanks again,  

[Qiu · Accepted solution]

@udles5666 
I followed your reply and maybe differ from what your really want.
but just a direction.

[udles5666]

Hi @Qiu 

Thanks heaps for the direction. I tweaked your solution a little bit as I needed Rank of a customer based on their state and region. Used a filter tool to group the sate & region and union the data again. Thanks again and have a great week ahead. 

[Qiu]

@udles5666 

Good to know it helps 😁