group/loop over a field

[becki]

I have a data set of dealership sales, for all dealerships.   for each dealership, i need to add up all the rows of sales, subtracting cancels of sales.  how do you loop over a field like that?  original data looks like :

yrmon       dealership_code transaction type   vin

032019        n98989              1                           <17 characters>

042019        n09090              -1

042019        n98989             -1

042019        n98989             -1

so i'd report:

032019     n98989        1

042019    n09090         -1

042019    n98989         -2

how do you loop over all yrmon combinations, adding up by dealership code, all sales minus cancels?

thanks

becki kain

[danilang · Accepted solution]

Hi @becki 

For this, you can use a simple summarize with the following config

 

giving 

Dan

[itestu · Accepted solution]

Hi Becki,

Just use the Summarize tool with this configuration : 

• group by | yrmon
• group by | dealership_code
• sum | transaction type

Irene

[becki]

thanks, both worked once i made the field i was summing on an int, not a string