Count Of Percentage >= 100

[Pradeepp]

Hi All

I need to count of persons achieved 100% or more than 100%. against the given target for  a selected date range. A sample excel sheet is also attached.

Area	BSM	Code	Name	Date	Target	Achievement	% Achieved
EAST	BSM1	A01	Anjan	01-12-2021	98	99	101%
EAST	BSM1	A02	Sajan	02-12-2021	78	56	72%
EAST	BSM1	A03	Aarati	03-12-2021	89	90	101%
EAST	BSM1	A04	Ramesh	04-12-2021	77	80	104%
EAST	BSM1	A01	Anjan	05-12-2021	56	59	105%
EAST	BSM1	A02	Sajan	06-12-2021	34	40	118%
EAST	BSM1	A03	Aarati	07-12-2021	55	54	98%
EAST	BSM1	A04	Ramesh	08-12-2021	85	78	92%
WEST	BSM2	B01	Bir Bahadur	01-12-2021	98	99	101%
WEST	BSM2	B02	Bijan	02-12-2021	78	80	103%
WEST	BSM2	B03	B Rama	03-12-2021	89	90	101%
WEST	BSM2	B04	Shyam	04-12-2021	77	80	104%
WEST	BSM2	B01	Bir Bahadur	05-12-2021	56	59	105%
WEST	BSM2	B02	Bijan	06-12-2021	34	33	97%
WEST	BSM2	B03	B Rama	07-12-2021	55	54	98%
WEST	BSM2	B04	Shyam	08-12-2021	85	78	92%

This the data and based on that following is the output as required.

Output		Period From	Period To	Count of Achieved	Count of Non Achieved
EAST	BSM1	01-12-2021	04-12-2021	3	1
WEST	BSM2	01-12-2021	04-12-2021	4	0
EAST	BSM1	05-12-2021	08-12-2021	2	2
WEST	BSM2	05-12-2021	08-12-2021	1	3

Thanks in advance

[Or]

I'd suggest adding a flag in script:

Load Area, BSM, Code, Name, Date, Target, Achievement, [% Achieved], if(Achievement >= Target, 'Achieved', 'Non Achieved') as HasAchieved

From YourTable;

In the front end you'd then use HasAchieved as a dimension and simply count(Name)

[anat]

@Or  how to derive "Period From" and "Period To" from Date field

[Or]

@anat The logic behind these fields wasn't specified, so I didn't try and set them up since the original post suggested it was a *selected* date range, which means it's just a filter on the Date field. Min and Max on the Date field would fetch the selections if it is necessary to display them in the table.