Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
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

 

 

5 REPLIES 5
MarqueeCrew
20 - Arcturus
20 - Arcturus

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 restart. Order shall return.
Please Subscribe to my youTube channel.
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
ACE Emeritus
ACE Emeritus

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