Count the frequency and output in every month

[fangyi189]

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!

[LordNeilLord]

Hey @fangyi189,

This workflow should give you the desired output:

[fangyi189]

Hi, this has helped a lot. (:  May I enquire why you did both join and right join for the join tool?

[fangyi189]

Hi,

I have this error after I changed the format of the date. Do you mind seeing the error for me? Been trying to solve it for awhile.

[LordNeilLord · Accepted solution]

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.

[fangyi189]

This new formula helps (: thank you!

[MAAbdullahAlMubarah]

@LordNeilLord  What About if there is no end date?