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

Calculate sum totals in a column based on criteria specified in two other columns

Solonglolli
7 - Meteor

Hi, I'm hoping someone can help me with this simple task.

I have looked at the examples of Summarize tool to better understand how to calculate totals, but the results in the workflow don't add up to the sum totals in the excel file pivot table that I'm comparing them to.

There are three columns in my data file containing information such as currency, value of payments and quarters, whereby each payment is displayed in a row (payments represent a single transaction) and each payment is associated with a quarter of the year (Q1/Q2/Q3/Q4) based on the transaction date .

 

I'd like to create a report where the column headers are the 4 quarters (Q1/Q2/Q3/Q4), the values are the sum of payment totals by quarter and by currency.

My raw data file is attached, as well as the Transform tool settings I used. If you notice, the output of the transform tool doesn't give me the same payment totals as in the original excel file pivot table. I have applied a number of filters before the transform tool, but i made sure that the output was matching with the raw data file output after filtering in excel. So there should be no reason why the alteryx output should not match the excel pivot outputs.

Could you please help how to build this workflow?

I've attached the original excel file with a screenshot of the pivot (that i'm trying to replicate in the workflow) and also the transform settings I applied and the output of it.

 

Thanks and apologies for the winded description.

3 REPLIES 3
AngelosPachis
16 - Nebula

Hi Solonglolli,

 

I think your cross-tab tool was configured properly. I couldn't figure out what went wrong in your workflow, the logic seemed to be ok. Please find attached a solution to your problem, the numbers between the excel pivot and the output seem to match.

 

Let me know if it worked for you 🙂

 

Annotation 2020-06-30 124223.png

Solonglolli
7 - Meteor

Hi Angelos,

 

 

Thank you for promptly responding. The mistake I made was I thought that the cross-tab tool would summarise the payments (mathematically) when selecting the method of aggregation. Now I see that I had to add a summarize tool prior to using the cross tab. I just have a question to the filter tool: I see you have used the formula tool to add a new record at the bottom of the TXN Currency column and called it "Grand total" but where is the formula, where did you tell the workflow to accumulate the totals of each following column and fill in the values in the remaining fields of the Grand Total row of the table?

Lastly, what does the Select tool serve as at the end of the workflow?

 

Regards,

Eva

AngelosPachis
16 - Nebula

Hi Eva,

 

On a second thought, the use of the summarise tool before the cross-tab tool is indeed unnecessary. Like you said, the aggregation can happen in the cross-tab tool. Find attached a workflow that's working without the summarize tool, the results are the same.

 

Concerning the formula tool, it is only used to create a string, a title, to fill in the TXN_CCY column. I did that to bring the table in exactly the same form as the Excel output you provided.The accumulation occurs in the cross tab tool, where the records of each quarter are summed and then pivoted.

 

Lastly, I used the select tool just to rearrange the columns position. If you compare the input and output anchors of the select tool, you will notice that the TXN_CCY column is moved from being last to being first.

 

Annotation 2020-07-01 181516.png

 

Please reach out if anything remains unclear.

 

Cheers 

 

Angelos

 

 

 

Labels