Conditional Subtraction Cell by Cell

[hiva]

Hello,

Thanks a lot for your contributions, I've learned a lot from you!

I need to modify my existing data so the sum of all totals by day match the "supposed" value I need to have for the day. It's a little confusing, here is an example:

Date	Total	Total Day	New Total After Formulas
1/1/2018	10	28	8
1/1/2018	10	28	10
1/1/2018	10	28	10
1/2/2018	10	15	5
1/2/2018	10	15	10
1/3/2018	10	9	9
1/4/2018	10	20	10
1/4/2018	10	20	10
1/5/2018	10	20	5
1/5/2018	10	20	5
1/5/2018	10	20	10

The column named "Total day" is the amount that the sum of total for each day was supposed to be. On 1/1/2018, the sum of totals is 30 but it's supposed to be 28, so I need to create a new column that subtracts the difference. If the amounts match, no modification is needed. I don't care if the subtraction goes row by row, or if the difference is subtracted from all rows on a specific date equally, but I need to have data on every row (see date 1/5/2018). Also, please note that my data is not ordered the way this one is, mine is all disorganized. 

Thanks a lot

[wdavis]

Hi @hiva 

What is the column 'New Total After Formulas' doing in this instance? 

If the values are different, in the instance of the 1/1/2018, where would the subtracted value need to appear?

What you are after can be achieved, I am just trying to understand your problem a little bit more to work out the best way to do this!

Kind Regards

Will

[danilang]

Hi @hiva 

Here's one way to go about adjusting your daily totals

"The Adjust daily totals" container does the actual work.  The Validation container just sums up the adjusted values to ensure so you can compare them to the "Total Day" column.  

Start off by adding a RecordID to return your original sort order at the end of the process.  Then convert the date into Alteryx format.  Sort by Date and recordID.  The bottom branch here calculates the actual sum of the "Total"  and the following formula computes the DeltaPerDay applied to each column and the DeltaLastDay which is used to adjust for the fact the the difference isn't usually divisible by the number of entries.  This is joined back to the original data and the Multi-Row formula subtracts the DeltaPerday for every row except the last one.  For the last row, the DeltaLastDay value is subtracted.  At this point, you have the Adjusted column which gives your new results.  The rest of the workflow sums this new value and resorts your data to the original order

 

I added an extra day to your sample data to show that the required total can be greater than the actual total as well.

Dan

[hiva]

Thank you very much! 

Unfortunately, my data has decimal points, and this workflow doesn't seem to work with decimals. How can I make it work? 

Also, could you please explain the purpose of the "validation" process? (just for learning purposes)

Thanks a lot!

[danilang · Accepted solution]

Hi @hiva 

Since your initial data had only integer fields(i.e. no decimals), I built the workflow to use integer fields so you wouldn't have partial quantities.  Since you can have partial quantities, I changed the field types to doubles and removed the functions that converted the various values to integer, i.e. Ceil().   

The purpose of the Validation section is proof that the new amounts add up to the Day total amounts the you need.  It's not needed for the actual results, since these are available at the output of the last tool in the "Adjust daily totals" container.   The way it works is that the summary tools adds ups all the Adjusted values for each day and then uses a join to add this total column "Calculated Daily Total" to each of the input rows.   

Dan