Help for a newcomer

[Kevin_Smith]

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.

Thanks in advance!!

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

[s_pichaipillai · Accepted solution]

@Kevin_Smith

attached workflow will give you some idea to prepare your data 

[Kevin_Smith]

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

[Kanderson · Accepted solution]

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

[Kevin_Smith]

Thanks a lot for your solution and patience with newcomers.