Alteryx Designer Desktop Discussions
Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
Attaching a sample workflow for your case. Hope this helps.
So, first things first, lets generate a column which is just the week number. This can be achieved using a string formula along the lines of...
tonumber(left([FieldB],2))
Let's save this as a new field called 'week' and convert the data type to 'double'
Let's repeat this step now for year, something along the lines of
tonumber(right([FieldB],4))
should suffice. Again lets save this as a new field called 'Year' and convert the data type to 'double'.
Next we need a method for isolating the last date, this can be done in many ways. I would use a sort tool, to sort your data by 'year' and then 'week' (it's key that it is in this order).
Now a final step we can use is to sample our data. Let's sample the last '12' records (because we want to look back 12 weeks. And in order to do this for each group, we must group on [FieldA].
This will leave us with 36 records. 12 records for A, 12 records for B and 12 records for C (you seem to be missing this from your output but I'm guessing that was by accident).
I've attached the sample solution for you.
Ben
I've attached an ammended workflow to cater for the additional business rule you wanted.
This time I summerize to find the last year and last week. I then use the generate rows tool (in a similar way to ponraj) to identify the 12 weeks we need to look for.
I then join this against our initial data stream.
By then performing a count for each product of the number of records, if it is equal to 12 we want to keep the product, if it is not then it must be a new product.
See attached.
Ben
Attaching updated workflow for your case after correcting the fault which @BenMoss highlighted. Hope it helps.,
as i use your idea simulate with real data. i found some of data that i would to filter out still showing.
May i explain what i would again.
1) I've actual sales rolling by week.
2) I would to filter out which product don't have actual sales some rolling last 12 week. if one of 12 month don't have actual sales supposed to be filter out also. I would only product have actual sales 12 week.
Filter in :
- product that have actual sales > 0 for all last 12 week
- product that have actual sales between week in last 12 week (w1-4 = value, w5-7 = 0, w8-12 = value)
Filter out:
- product that have actual sales = 0 since first week until some of 12 week ( ex. w1 = 0, w2 = 0, w3 = 4, w3-12 = value)
- example data need to filter out in screenshot.
