Dynamic last 6 weeks sales calculation

[Bryth]

Hello Team,  I am trying to create a calculation logic in Alteryx, that dynamically calculates the last 6 weeks sales. Can someone help me with this?  I don't have a date filed but a week filed that is in string data type. 

[Felipe_Ribeir0]

Hi @Bryth 

If you cold provide a sample input/expected output dataset, it would help a lot.

[cjaneczko]

Is it only one year of data? If you have multiple years of data how do you differentiate a week in 2023 vs 2022 without a date field?

[jbravman_]

what does your week field look like? 

[Bryth]

I have this sales data and they are grouped by weeks. I want to dynamically get the last 6 weeks data for 2022 and 2023 data. My difficulty is that I don't have a date field format and the week filed is a string format

[jbravman_]

If you have the week number you can convert it to a date, like the first day of the week and run off that. 

[Bryth]

For instance I have this current year and Last year Sales Data with weeks field. I want to   calculate the  6 weeks trend data by dynamically calculating the the last 6 weeks sales data . I want to know the logic in Alteryx to write a formuale to look back at the last 6 weeks data. 

[rzdodson]

@Bryth I have attached a workflow containing a number of potential solutions for you based on your use case and workflow complexity.

Personally, I like the Analytical App approach because it enables you to customize your data retrieval without having to go in to the workflow and change numerous tools.

Hope that helps out!

[jdminton · Accepted solution]

I think this way will work with your data. We don't see all of the columns, but if there is not a column for current and LY sales for each week, you will need to add a multirow formula to calculate first. Let me know if this works for you.