topic Re: abc classifciation calculation on sales history in Alteryx Designer Desktop Discussions

abc classifciation calculation on sales history

Pascal_R — Tue, 22 Sep 2020 20:45:32 GMT

Hi all,

I have Material number and the sales data last past 6 months.

How can I calculate the classification if I have to consider below ABC rules:

A= 50% B=30 C=15 D=5

Please see attached example of dateset.

Thank you for any help on this.

Pascal

Re: abc classifciation calculation on sales history

jdunkerley79 — Tue, 22 Sep 2020 21:14:26 GMT

Can you give more detail on what you want the result to be?

Do you want to allocate the QTY of each material into four classifications (A-D)?

Re: abc classifciation calculation on sales history

Pascal_R — Wed, 23 Sep 2020 05:15:18 GMT

@jdunkerley79

Please see attached excel file where you can see what I need to try in Alteryx.

Hope this helps to understand it more.

Please let me know.

Regards,

Pascal

Re: abc classifciation calculation on sales history

atcodedog05 — Wed, 23 Sep 2020 06:23:07 GMT

Hi @Pascal_R ,

Here a workflow for the task.

Input

Output. Getting expected output.

Hope this helps. Please check and let me know.

Please mark this post as solution if it solves your requirement.

Re: abc classifciation calculation on sales history

Pascal_R — Wed, 23 Sep 2020 06:18:26 GMT

Hi @atcodedog05

Thank you very much for your fast replay.

If I try to open the workflow I got attached error.

Are you able to send it in the yxmd format?

Regards,

Pascal

Re: abc classifciation calculation on sales history

grazitti_sapna — Wed, 23 Sep 2020 06:18:33 GMT

Hi @Pascal_R , give this a try if it works for you.

If this is what you are looking for then please mark this post as solution/

Thanks.

Re: abc classifciation calculation on sales history

atcodedog05 — Wed, 23 Sep 2020 06:20:42 GMT

Hi @Pascal_R

Please find the workflow.

Re: abc classifciation calculation on sales history

jdunkerley79 — Wed, 23 Sep 2020 06:20:00 GMT

I'd suggest:

- Sort into ascending QTY

- Use a running total tool to create a running total of QTY

- Use a summarise tool to make the grand total

- Use an append field to add the grand total to all rows

- Calculate the PctTotal as (RunningTotal - QTY) / GrandTotal: this give % used at start

- Allocate to A,B,C,D. I used a formula tool:

IF [PctTotal]<0.05 THEN "D" ELSEIF [PctTotal]<0.2 THEN "C" ELSEIF [PctTotal]<0.5 THEN "B" ELSE "A" ENDIF

Sample attached

Re: abc classifciation calculation on sales history

Pascal_R — Wed, 23 Sep 2020 07:37:06 GMT

Hi @grazitti_sapna

Would that also be possible if I have date from different countries and need to do the ABCD analysis then split by country?

Please see attached example data as a starting poin.

Regards,

Pascal

Re: abc classifciation calculation on sales history

grazitti_sapna — Wed, 23 Sep 2020 08:26:43 GMT

@Pascal_R , give this a try.

In running total tool group by plant so it will give you result as required.

Thanks.

Re: abc classifciation calculation on sales history

Pascal_R — Wed, 23 Sep 2020 08:37:27 GMT

HI @grazitti_sapna

I need to have the ABCD classification per Plant now I see it over all Plants.

What I need to see is as an example in Plant 1500 their own ABCD and there the same for each other Plants.

In your workflow its spread over all the Plants any solution for that?

Regards,

Pascal

Re: abc classifciation calculation on sales history

grazitti_sapna — Wed, 23 Sep 2020 09:09:58 GMT

Hi @Pascal_R, will it be possible for you to share an example of desired output?

Thanks.

Re: abc classifciation calculation on sales history

Pascal_R — Wed, 23 Sep 2020 09:13:38 GMT

Hi @grazitti_sapna

Please see attached file. Hope this helps to understand the needed result.

Regards,

Pascal

Re: abc classifciation calculation on sales history

grazitti_sapna — Wed, 23 Sep 2020 09:31:00 GMT

Hi @Pascal_R , pleaser refer to the workflow attached along with the screenshot.

Let me know in case of any queries.

Thanks.

Re: abc classifciation calculation on sales history

Pascal_R — Wed, 23 Sep 2020 09:37:17 GMT

@grazitti_sapna

Thank you so much! If there would be an additional parameter within the Plant like within the Brand todo an ABCD I only need to summarize it?

Or is there anything else I need to think about?

Regards,

Pasal

Re: abc classifciation calculation on sales history

Pascal_R — Wed, 23 Sep 2020 10:15:56 GMT

@grazitti_sapna

Please see attached file showing you in within Hierarchy level.

I guess only Summary on Hierarchy level is needed right ?

Regards,

Pascal

Re: abc classifciation calculation on sales history

grazitti_sapna — Wed, 23 Sep 2020 10:48:34 GMT

Hi @Pascal_R , yes it will work with summarize tool.

Please refer to the config below.

Thanks.

Re: abc classifciation calculation on sales history

grazitti_sapna — Wed, 23 Sep 2020 11:10:13 GMT

@Pascal_R , please find the attached workflow.

Thanks.