Is it possible to filter out and create a new column Based on that

[Kinetic_analytics]

Hello:

For the ollowing information, I want to create a Column based on all Expense and the another column is based on Net_AnL . Then I want to deduct (Expense- Net_anL)

Amount	Account Type
-385314	Net_AnL
45862	Expense
-59875	Net_AnL
-587	Expense
74981	Expense
651779	Expense
981872	Net_AnL
19074777	Expense
19601074	Net_AnL
5561118	Net_AnL

Appreciate your help.

Thanks

[Felipe_Ribeir0 · Accepted solution]

Hi @Kinetic_analytics 

Not sure if it is it, please confirm if i understood correctly.

[Kinetic_analytics]

Do you mind to attach the workflow for me? Thanks

[DataNath · Accepted solution]

As you already have the necessary categories in the data itself, you can just cross-tab to get the headers and the aggregated values that correspond to them which Alteryx needs for deductions because formulas work on a row level. After that, it's just a simple subtraction within the formula tool:

[Felipe_Ribeir0 · Accepted solution]

Hi @Kinetic_analytics 

Please check it again.

[IraWatt · Accepted solution]

Hey @Kinetic_analytics,

You can group by account type and sum the amounts using the Summarize tool then pivot the data using the crosstab tool then do your formula using the formula tool:

This solution uses the cross tab tool which can be hard to understand to new users. I would recommend checking out the community Getting-Started-Learning-Path for some context on how they work: https://community.alteryx.com/t5/Learning-Paths/Getting-Started-Learning-Path/ta-p/475117#done

Any questions or issues please ask

Ira Watt
Technical Consultant
Watt@Bulien.com 

[BrandonKO]

From how I'm understanding your question, you will want to use the Crosstab tool to pivot the data. Your "Account Type" will be the Column Headers, and the "Amount" will be the Values for New Columns. Then you would use a formula tool to calculate the difference between each column.  

[Kinetic_analytics]

Thank You. I really Appreciate all of your help. What a nice community.