Help for a newcomer

Question

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                MonthCustomerProductCurrencyQuantitySales Value (Curr)Sales Value (EUR)  JanAYEUR62                                 5 518                                   5 518    JanAZEUR53                                 4 717                                   4 717    JanBYGBP90                                 5 760                                   7 200    JanBZGBP68                                 5 440                                   6 800    FebAYEUR82                                 6 970                                   6 970    FebAZEUR62                                 6 138                                   6 138    FebBYGBP91                                 7 134                                   8 918    FebBZGBP51                                 3 672                                   4 590             Target                 CustomerProductCurrencyJAN QuantityFEB QuantityJAN Sales Value (Curr)FEB Sales Value (Curr)JAN Sales Value (EUR)FEB Sales Value (EUR)AYEUR62                     82                                   5 518  697055186970AZEUR53                     62                                   4 717  613847176138BYEUR90                     91                                   5 760  7134,472008918BZEUR68                     51                                   5 440

367268004590

s_pichaipillai · Accepted Answer

@Kevin_Smith

attached workflow will give you some idea to prepare your data

Kanderson · Accepted Answer

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