Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

General Discussions has some can't miss conversations going on right now! From conversations about automation to sharing your favorite Alteryx memes, there's something for everyone. Make it part of your community routine!

SOLVED

Convert Sales to Percentage

jmarleigh
7 - Meteor

Hi,

 

I have a situation where I'm trying to convert the sales units at a store/sku level to a percentage of the sales at the highest selling store and then for every 17% attribute an A through F grade.

 

I'm just wondering how I would go about doing this?

 

The ranking would look something like below:

 

Percent Rank Range

 

Upper Limit

Lower  Limit

Store
Category

100.0%

83.9%

A

83.8%

67.4%

B

67.3%

50.8%

C

50.7%

34.2%

D

34.1%

17.1%

E

17.0%

0.0%

F

 

And the data that I'm using would be setup like below and I would be wanting to calculate the final two columns.

 

StoreSKUSalesPercentage of Total SalesRank
104310014100%A
10441001375%B
10451001250%D
104310024100%A
10441002375%B
10451002250%D

Thanks,

Justin

 

 

MarqueeCrew
19 - Altair
19 - Altair

Attached is a workflow solution that uses a TILE (manual) to achieve your results.  Please try it and see if it solves your challenge for you.

 

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
jmarleigh
7 - Meteor

Hi,

 

Thanks for the file, but because you're using a newer version of Alteryx I'm unable to open it. Any chance you're able to save it for older version? I'm using 11.0.6.28907.

 

Thanks,

Justin

NicoleJohnson
14 - Magnetar
14 - Magnetar

Alternative solution (because it wouldn't be a normal day in the Alteryx Community if @MarqueeCrew and I didn't reply to all the same things at almost the same time! haha)

 

This method would allow a bit more of a dynamic assignment of the % cutoffs (rather than manual entry in the tile tool) by updating the input that contains the limit parameters. Used the Append tool to append the Upper & Lower limits to the data, then filtered to show only those lines that fell within the limits. Also included the calculations for getting the %'s.

 

Hope these help! 🙂


Cheers,

NJ

mborriero
11 - Bolide

@MarqueeCrewI love your solution. It is always good to see all the different approach to the same use case.

 

I would have just used a formula tool with an IF statement.

 

if [Percentage of Total Sales] <= 1 and [Percentage of Total Sales] >= 0.839 then "A"
elseif [Percentage of Total Sales] <= 0.838 and [Percentage of Total Sales] >= 0.674 then "B"
elseif [Percentage of Total Sales] <= 0.673 and [Percentage of Total Sales] >= 0.508 then "C"
elseif [Percentage of Total Sales] <= 0.507 and [Percentage of Total Sales] >= 0.342 then "D"
elseif [Percentage of Total Sales] <= 0.341 and [Percentage of Total Sales] >= 0.171 then "E"
elseif [Percentage of Total Sales] <= 0.170 and [Percentage of Total Sales] >= 0.0 then "F"
else ""
endif

mborriero
11 - Bolide

You can just open the file with a text editor and replace 11.3 with 11.0.

Labels