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

Help for a newcomer

Kevin_Smith
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.

 

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
4 REPLIES 4
s_pichaipillai
12 - Quasar

@Kevin_Smith

 

attached workflow will give you some idea to prepare your data 

Kevin_Smith
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

 

 ABCDEFG
1CustomerProductCurrencyJAN QuantityFEB QuantityJAN MIXJAN Mix x FEB QTY
2AYEUR62                                 82  23 %                                 65  
3AZEUR53                                 62  19 %                                 56  
4BYEUR90                                 91  33 %                                 94  
5BZEUR68                                 51  25 %                                 71  
6   273286100 %                               286  
Kanderson
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:

 

% Total.PNG

 

Summarize tool:

sum.PNG

 

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

 

Formula tool: 

formula.PNG

 

That should do it

Kevin_Smith
5 - Atom

Thanks a lot for your solution and patience with newcomers.

Labels