Count Inwards and Outwards from one period to another period

I have a set of data that has inconsistent dates and comes in twice a month with the dates this month different from last month’s. For example, for February, I have data come in at 02/09/2021 and 02/24/2021. In January, data came in 01/03/2021 and 01/26/2021. I’m trying to do a period over period count, where I could compare the difference between 02/24/2021 and 01/03/2021 or 02/09/2021 compared with 01/26/2021. Pretty much looking at one period and the other period side by side with the difference. Is there a way I can do this in Knime?

Thanks in anticipation for your help!

So will your data always have the same number of entries for each month?

Does it always appear in date order in which case can you join one month with another based on rowid to do your comparison? If not in date order, can you sort each set of data into date order?

I would think that this would be your starting point, and then yes knime should be able to do this comparison for you. Does that help at all?

Or are you saying all the data is on in a single table, in which case some other manipulation will be required but not insurmountable. Post some sample data and elaborate a little more if you need more ideas.

1 Like

@makawhy Having thought this through slightly more, take a look at this sample workflow

Is this on the right lines, or at least give some ideas?

1 Like

Hi @takbb, Thanks for helping out. The data is in a single table. I’m trying to show 2 periods by Location number counts, but I have inconsistent dates months in and out and their difference in columns. I attached a sample data here. LOCATION COUNT BY PERIOD.xlsx (11.4 KB)

Thank you @takbb! I’ll play around with this flow. Hopefully this resolves it.

Ok, so possibly if it were me I’d look at reading the excel and maybe adding a column which is just the month for each row, then splitting that into two flows, by month, using a row filter.

You could then use a group by on each of those to get a count, grouped by date, from each of those flows, so that should leave you with two datasets each containing its two dates and the count for those dates. Then that would feed into something like the flow above. Possibly?

1 Like

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.