Hi all, I have a dataset like this : ActivityStart_dateEnd_dateTotal number of daysRunning01/01/1701/01/171Dancing03/01/1704/01/172Squash20/04/1720/04/171Running23/04/1725/04/173 and I want the output to be like this ActivityAprilMayJuneJulyAugSeptOctDecJanRunning300000001Dancing000000002Squash100000000 So it'll count the total number of days and put it according to the months. It starts at April due to the financial year. Do you guys have ideas how to output it out like the table above? Thanks!

Count the frequency and output in every month

Hi all,

I have a dataset like this :

Activity	Start_date	End_date	Total number of days
Running	01/01/17	01/01/17	1
Dancing	03/01/17	04/01/17	2
Squash	20/04/17	20/04/17	1
Running	23/04/17	25/04/17	3

and I want the output to be like this

Activity	April	May	June	July	Aug	Sept	Oct	Dec	Jan
Running	3	0	0	0	0	0	0	0	1
Dancing	0	0	0	0	0	0	0	0	2
Squash	1	0	0	0	0	0	0	0	0

So it'll count the total number of days and put it according to the months. It starts at April due to the financial year.

Do you guys have ideas how to output it out like the table above?

Thanks!

Workflow

Preparation

Accepted answers

LordNeilLord

Hey @fangyi189,

Weirdly I didn't get an error when I ran your workflow (but I expected one).

In my original version I had used DateTimeParse, to convert your date into a proper date..but with your dates as they are this is no longer needed. I have updated the formula to reflect this.

In answer to your other question, I union'd the R & J outputs of the join because I wanted to make sure that I had an entry for every month regardless of whether there was an activity value for that month.