If i've actual sale by week rolling, I would to filter only product have actual sales last 12 week. How can i create work flow. A01.2018948941A02.20182222543A03.20188004863A04.20187784038A05.20186197539A06.20187886544A07.20187379607A08.20182203715A09.20183017844A10.20187679522A11.20186518827A12.20181265694A13.20189018866A14.20187543925A15.201823512A16.20182200521A17.20186433105A18.20188700647A19.20183666616A20.20187613239A21.20185974221A22.20186188258A23.2018577347A24.20187344316A25.20186207304B01.20180B02.20180B03.20180B04.20180B05.20189852260B06.20186414161B07.20182599728B08.20182169699B09.20182241194B10.20186817776B11.20182637558B12.20186232087B13.20187809928B14.20186034553B15.20186722189B16.20185351556B17.20189596153B18.20182150359B19.20182574456B20.20181763329B21.20188610813B22.20185532745B23.20185560542B24.2018271180B25.20186968067C05.20180C06.20180C07.20180C08.20180C09.20180C10.20180C11.20180C12.20180C13.20180C14.20180C15.20180C16.20180C17.20180C18.20180C19.20180C20.20180C21.20185260949C22.2018284634C23.20182215067C24.20188766827C25.20188390595 from the above table to the below table. A14.20187543925A15.201823512A16.20182200521A17.20186433105A18.20188700647A19.20183666616A20.20187613239A21.20185974221A22.20186188258A23.2018577347A24.20187344316A25.20186207304B14.20186034553B15.20186722189B16.20185351556B17.20189596153B18.20182150359B19.20182574456B20.20181763329B21.20188610813B22.20185532745B23.20185560542B24.2018271180B25.20186968067

Filter data

If i've actual sale by week rolling, I would to filter only product have actual sales last 12 week. How can i create work flow.

A	01.2018	948941
A	02.2018	2222543
A	03.2018	8004863
A	04.2018	7784038
A	05.2018	6197539
A	06.2018	7886544
A	07.2018	7379607
A	08.2018	2203715
A	09.2018	3017844
A	10.2018	7679522
A	11.2018	6518827
A	12.2018	1265694
A	13.2018	9018866
A	14.2018	7543925
A	15.2018	23512
A	16.2018	2200521
A	17.2018	6433105
A	18.2018	8700647
A	19.2018	3666616
A	20.2018	7613239
A	21.2018	5974221
A	22.2018	6188258
A	23.2018	577347
A	24.2018	7344316
A	25.2018	6207304
B	01.2018	0
B	02.2018	0
B	03.2018	0
B	04.2018	0
B	05.2018	9852260
B	06.2018	6414161
B	07.2018	2599728
B	08.2018	2169699
B	09.2018	2241194
B	10.2018	6817776
B	11.2018	2637558
B	12.2018	6232087
B	13.2018	7809928
B	14.2018	6034553
B	15.2018	6722189
B	16.2018	5351556
B	17.2018	9596153
B	18.2018	2150359
B	19.2018	2574456
B	20.2018	1763329
B	21.2018	8610813
B	22.2018	5532745
B	23.2018	5560542
B	24.2018	271180
B	25.2018	6968067
C	05.2018	0
C	06.2018	0
C	07.2018	0
C	08.2018	0
C	09.2018	0
C	10.2018	0
C	11.2018	0
C	12.2018	0
C	13.2018	0
C	14.2018	0
C	15.2018	0
C	16.2018	0
C	17.2018	0
C	18.2018	0
C	19.2018	0
C	20.2018	0
C	21.2018	5260949
C	22.2018	284634
C	23.2018	2215067
C	24.2018	8766827
C	25.2018	8390595

from the above table to the below table.

A	14.2018	7543925
A	15.2018	23512
A	16.2018	2200521
A	17.2018	6433105
A	18.2018	8700647
A	19.2018	3666616
A	20.2018	7613239
A	21.2018	5974221
A	22.2018	6188258
A	23.2018	577347
A	24.2018	7344316
A	25.2018	6207304
B	14.2018	6034553
B	15.2018	6722189
B	16.2018	5351556
B	17.2018	9596153
B	18.2018	2150359
B	19.2018	2574456
B	20.2018	1763329
B	21.2018	8610813
B	22.2018	5532745
B	23.2018	5560542
B	24.2018	271180
B	25.2018	6968067

Tips and Tricks

Accepted answers

BenMoss

@winnie12 does this get you to where you wanted?

Ben

Latest_Remake.yxzp

All comments

ponraj

Attaching a sample workflow for your case. Hope this helps.

Workflow Results Results

Filter data.yxmd

BenMoss

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

Sample Solution.yxmd

winnie12

Thank you for you solution but it can't solve my issue. I would to filter only which product have actual sales last 12 week only. if some last 12 week don't have actual sales i would to filter out because that are new product.

BenMoss

@ponraj be careful here, as we are working with dates you need to remember that if we go into 2019 the weeks will likely restart at 01.2019 and so on.

In your situation if we have say 5 weeks from 2019 and then all of our 2018 data. The 2018 data will be selected as 52.2018 is selected as the latest date, which is not true. Your generate rows method would also fall down too.

Ben

ponraj

Attaching updated workflow for your case after correcting the fault which @BenMoss highlighted. Hope it helps.,

Filter data.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703