1 Reply Latest reply on Aug 13, 2019 6:47 AM by Jonathan Drummey

    Running SUMIF in Tableau Prep

    Bob Janson

      Hi Tableau Prep Community!

       

      I'm starting out with Tableau Prep and was wondering if my current problem could be solved using Tableau Prep as opposed to the Excel solution I'm currently running:

       

      I'm processing a forecast for a logistics company where the input is as follows:

       

      Week - Customer - Port From - Port To - Amount of shipments

       

      They have in the forecasting process however, the option to forecast to "Other" as Port To field. We allocate the "Other" shipments to actual ports based on historical data. This requires a 'lottery' within my Tableau Prep to take place to allocate the tanks properly.

       

      For example:

       

       

      So we have 7 forecasted shipments from port Kaohsiung to Other in the forecast, historically it can go to any of the 11 ports listed with the dividing % being the % of historical shipments going to those ports. To allocate these 7 tanks I need the following things:

       

      1. A Running SUMIF adding up Dividing % along the table, so the values would be 0,08, 0,32, 0,32, 0,38 and so forth in the screenshot above,

      2. The previous aggregate to see if the column "DividingNumber" is between or equal to the current & previous aggregate.

       

      In this instance the 7 jobs would be allocated to the second lane in the list, from Kaohsiung to Bangkok. The previous aggregate is 0,08, the new aggregate is 0,32 and the dividing number of 0,15 is between these values. Thus allocation to this lane.

       

      In Excel this is fairly easy but I would like my entire data flow to be incorporated to Tableau.

       

      Any help would be greatly appreciated! Hope someone can crack this nut

       

      Bob

        • 1. Re: Running SUMIF in Tableau Prep
          Jonathan Drummey

          Hi Bob,

           

          Here are some ideas to get you started:

           

          In general in Prep this kind of algorithm is typically implemented one or more aggregations and/or calculations (potentially with some filtering as well) that are then joined back to the “main” data flow. So the major Steps you’ll use are Aggregate and Join steps, potentially with some Clean steps.

           

          SUMIF() can be done in Prep in one or two steps.

          The one step operation is to use an Aggregate step where the aggregation is using the necessary grouping. If the IF condition is complex then you might need to create a calculated field or do filtering before the Aggregate step.

           

          “Carrying” or “bringing forward” a value in Prep is more complicated at this time. You’ll need at least two steps - a calculated field in a step to create the necessary offset on the date and then a Join step to join the prior to the current. For example if you are doing a one week offset then you’d add a week to the date - I usually call this new calculated field something like “Date for Join” and then join on the other key field(s) and then Date for Join = Date. Also note in the Join you’ll have to pay attention to your desired business logic and left/inner/right semantics if your data is sparse (and potentially do some date padding).

           

          If you need more assistance then I suggest you post some sample data in a packaged flow and your work so far and let us know where you’re needing help.

           

          Jonathan

          1 of 1 people found this helpful