Count of closed

[BRRLL99]

Hi Team,

for the given dataset, im looking to add two new columns CLOSED ABOVE, CLOSED BELOW

if close>open , then i would like to know the count of particular month and year , how many times close>open vice-versa

ex: june-2022 closed above 6 times etc,

DATE	OPEN	CLOSE
11-07-2022	1,741	1,731
12-07-2022	1,738	1,742
13-07-2022	1,737	1,739
14-07-2022	1,764	1,736
11-08-2022	1,863	1,870
12-08-2022	1,896	1,863
13-08-2022	1,895	1,911
14-08-2022	1,886	1,891
14-04-2022	1,981	1,974
13-04-2022	1,970	1,984
12-04-2022	1,957	1,976
11-04-2022	1,949	1,948

expected result
Month	Year	Closed above	Closed below
june	2022	6	4
march	2022	3	8

[AngelosPachis · Accepted solution]

Hi @BRRLL99 

You can bring the data in the format you need by extracting the info for month and year from your date field and then creating a new column for whether the close value is above/beyond the open value.

Then with a summarize tool you can count the number of times for each month and then cross tab

Hope that helps,

Angelos

[binuacs]

@BRRLL99 one way of doing this