Count sales by month customer

[dataman]

Hi!
How could I create a column that knime count sales by line/month?

I need to to as example below:

|Customer|Date|Sales | Count (this is what I want to Knime to create|
|A |1 |34 |1 |
|A |2 | |0 |
|A |3 |23 |1 |
|B |1 |22 |1 |
|B |2 |12 |1 |
|B |3 |32 |1 |

[daniela_digles]

Hi @dataman ,
you can achieve this with the GroupBy node. Use Customer and Date as Group columns, and add Sales in the manual aggregation tab. First you need to define it to return the actual sales (see question below), then add it a second time to create the counts. As aggregation method, add count and make sure to remove the tick on “Missing”, so that you don’t count the missing sales (A 2 in your example). For this, all missing sales need to be correctly formatted as missing values (with the ? symbol in the table).

For adding the Sales column, I need more information on how the input could look like. Could Customer “A” in month 1 have several sales? e.g.:
|A|1|34
|A|1|20
If yes, should the output have two rows, where each has count 2? Or do you prefer just one row, where both sales are somehow combined (e.g. with comma)? Or one row, with only the first sale included, but a count of 2? Let me know how this should look like, then I can give you some suggestions on how to solve this.