Ok folks, not sure if this has been address here before but I tried to do a search without success.
I've been asked to calculate the total interest by month for the last 13 months. My input files are labeled (base201804, base201805, et cetera), (margin201804, margin201805, et. cetera). They will vary month to month but will always be 13 months. I need to calculate formula that will have as a header (Total201804, Total201805, et. cetera) and will be the addition of the margin201804+base201804.
Any of you gurus can point me in right direction where the header label and the formula will be able to point to the correct base and margin values?
Hello @D12monkey. I dont know if you idea is only sum by fields sufix (margin + base) and add new columns with prefix 'Total'. If is the idea, i have a example:
But in this case, is only with one row; the idea is transpose the fields and apply regex by sufix of fields (example 201805), then sum the values by sufix and join by position (because is only a one row). If you have more of 1 row, you need add a ID by row and in the traspose, apply group by ID ; like this:
@atrozz Thanks, I was heading down this path and was drawing a blank. The hiccup I was running into is that the the excel formula that I am trying to replicate is and have been able to do without dynamically changing the column names is
IF [Classification]="Excluded" THEN 0 ELSEIF [Classification]="L&D" THEN [Margin201804] ELSE [Margin201804]+ [Base201804] ENDIF
Only thing I can think of is filtering each Classification and doing the summary by that way and unioning the columns with the same name.
Exactly, it is an idea that you can pose. By using filter, you can have different flows for each one. If you filter by "excluded", simply pivot to use the formula and add to the field of totalxxxx the value of 0. You would also do the cases where the value is "L & D", but this time you add marginxxxx as assigned value; Finally, for the rest of the cases you can use the example that I presented to you, only that you must handle the groupings well since you manage several fields. Good luck!!!