Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
@Or how to derive "Period From" and "Period To" from Date field
@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.