Hello all,
I have a data problem wherein I need to sum for each month all negative values and paste the values in a new row and all positive values and paste the values in an other row. Here is my sample dataset:
Group | Month1 | Month2 | Month3 |
A | -5 | 8 | 3 |
B | 4 | 2 | 1 |
C | -10 | -5 | 0 |
D | 2 | -2 | -1 |
The result would be
Group | Month1 | Month2 | Month3 |
Negative | -15 | -7 | -1 |
positive | 6 | 10 | 4 |
My problem is that i need to apply this formula on several columns (more than 100). Without a conditional statement "IF month value > 0", I would have used a Summarize tool. But in my case, I d'ont see how to solve my problem.
Could you please help me on this?
Thanks
Solved! Go to Solution.
Hi @mmad!
I think the key here is that you want to Transpose your data first. From here, it is easy to sum all of your columns. You will just first want to Filter any numbers that are less than 0. Then use a Summarize to group by the month name and sum the values. Lastly, use a Cross Tab to get the data back in the right format, use a Formula tool to add your title field, and Union to get all of the results together.
See the attached workflow for an example!
Hi @mmad
Here's a solution with Cross-Tab tool:
- Transpose your columns
- Formula Tool to apply negative and positive clause
- Cross-Tab Tool to summarize.
WF attached.
Cheers,
Are @patrick_digan and I the only ones in the whole universe who do vertical??
@Kenda Yes 😁