How to sum multiple rows with a conditional statement

[mmad]

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

[Kenda · Accepted 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!

[Thableaus · Accepted solution]

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,

[MarqueeCrew]

@Kenda ,

After turning my monitor sideways, I like it :)

Cheers,

Mark

[Kenda]

Are @patrick_digan and I the only ones in the whole universe who do vertical?? 

[tmlmark]

@Kenda Yes 😁