alteryx Community

# Alteryx Designer Desktop Discussions

SOLVED

## Help for a newcomer

5 - Atom

Hi,

I am trying to solve a problem that I have traditionally done inside Excel, but want to use Alteryx to mass solve this problem. Perhaps the example would speak more than words.

This below is what I am trying to do, and from here I can calculate the impact of currency, mix and line item price changes on overall average price levels, but I need to get the data in this format to do it. So this is a simple example, but naturally in real life there can be cases where some customers buy something in one month and not the next, so there would be blank fields, and the data would need to be summarised on customer, product and currency (no duplicates of these combininations).

Then for bonus points if the source data had years worth of data and then how to select it so that the user could select what was the baseline month and what the comparison month would be. In this case below there are Jan vs Feb, but could equally be Q1 vs Q2 or 2015 vs 2016.

Kevin

 Source Data Month Customer Product Currency Quantity Sales Value (Curr) Sales Value (EUR) Jan A Y EUR 62 5 518 5 518 Jan A Z EUR 53 4 717 4 717 Jan B Y GBP 90 5 760 7 200 Jan B Z GBP 68 5 440 6 800 Feb A Y EUR 82 6 970 6 970 Feb A Z EUR 62 6 138 6 138 Feb B Y GBP 91 7 134 8 918 Feb B Z GBP 51 3 672 4 590 Target Customer Product Currency JAN Quantity FEB Quantity JAN Sales Value (Curr) FEB Sales Value (Curr) JAN Sales Value (EUR) FEB Sales Value (EUR) A Y EUR 62 82 5 518 6970 5518 6970 A Z EUR 53 62 4 717 6138 4717 6138 B Y EUR 90 91 5 760 7134,4 7200 8918 B Z EUR 68 51 5 440 3672 6800 4590
4 REPLIES 4
12 - Quasar

@Kevin_Smith

attached workflow will give you some idea to prepare your data

5 - Atom

Thankyou! This has set me off for another hour or two on the problem, now I have hit the next road block. So the calculation of column F below.

So column F is what % of sales that line represents in that month, so F2 is 62 / 273 and so on.

Column G is the result of column F multipled by the sum of column E, after this I have solved this puzzle.

I know that the summarize tool can make the sub totals but don't know how to easliy join things back up again.

Regards,

Kevin

 A B C D E F G 1 Customer Product Currency JAN Quantity FEB Quantity JAN MIX JAN Mix x FEB QTY 2 A Y EUR 62 82 23 % 65 3 A Z EUR 53 62 19 % 56 4 B Y EUR 90 91 33 % 94 5 B Z EUR 68 51 25 % 71 6 273 286 100 % 286
10 - Fireball

You only need to add one more summary tool to grab the sum from column D. You can then use the append fields tool to make a new column that contains that total. From there you can use a simple formula field to divide you quantity by your total and to multiply your mix score. Adjusting the workflow that was attached earlier you could add the following after the join:

Summarize tool:

Append Fields: Your target field will be your new sum... leave the source fields alone.

Formula tool:

That should do it

5 - Atom

Thanks a lot for your solution and patience with newcomers.

Labels