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

Here's a good challange I've been tasked to do.

D12monkey
8 - Asteroid

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?

 

Thanks in advance.

3 REPLIES 3
atrozz
8 - Asteroid

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:

labeled example.png

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:

labeled example2.png

Regards and good luck!!!

D12monkey
8 - Asteroid

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

 

Your workflow got my juices going. Thanks!

atrozz
8 - Asteroid

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!!!

Labels