Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Everyone,
I have a graph with monthly trend where in I need to show the % of Rejected Candidates.
Formula would be "total number of Offers rejected / total number of Offers made"
Here the issue is I am having different dates for both offer rejected and offer made then how do I select the Month-Year in the dimension.
I can't either take Offer Rejected Month neither Offer Made Month.
Sample data would be like this:
SO No | Candidate No | Indent Status | Offer Rejected Date | Declined reason | OfferMade |
73 | 10353 | Closed | 21-Oct-10 | Not Interested | 24-Aug-10 |
73 | 10354 | Closed | 22-Oct-10 | Tech Rejection | 24-Sep-10 |
74 | 10355 | Closed | 23-Nov-10 | Not Interested | 25-Oct-10 |
75 | 10356 | Closed | 24-Oct-10 | Tech Rejection | 26-Sep-10 |
I want do count the candidates who have rejected the offer vs offers made
How do I solve this?
Regards,
Tauceef
Hi Tauceef,
I believe you need to choose to plot the % of rejected candidates either by OfferMade or OfferClosed (I assume you have field like this in your data > date that signifies when was the offer closed, if it was rejected or accepted).
You can use techniques like "Canonical Date" calendar (Canonical Date) which will allow you to plot both counts (offered as well as rejected) on a single Month-Year dimension. But if you need to plot the ratio, it will have to be just by one of the available dates.
Regards,
Radovan
maybe this:
Count( {< [Offer Rejected Date] = {"*"} >} Distinct SONo )
/
Count( Distinct SONo )
Hi Andrea,
Thanks for your quick response.
I am actually looking for the Month-Year dimension. How do I take the Month-Year so that I will get count of both Rejected and Offers Made for the respective Month to calculate % in the chart.
Regards,
Tauceef
Try
Date(Date3((Year(Date)*100)+Month(Date),'YYYYMM'),'MMM-YY')
try this script:
QUALIFY '*';
UNQUALIFY '%*';
Tab1:
LOAD *,
AutoNumber(%PK, [%Date field]) as %Key0;
LOAD
[SO No] as %PK,
[Candidate No],
[Indent Status],
[Offer Rejected Date] as [%Date field],
if( Not Isnull([Offer Rejected Date]), 1, 0 ) as %Rejected,
[Declined reason]
From YourTable;
Tab2:
LOAD *,
AutoNumber(%PK, [%Date field]) as %Key1;
LOAD
[SO No] as %PK,
[Candidate No],
[Indent Status],
[Declined reason],
[OfferMade] as [%Date field]
From YourTable;
LinkTable:
LOAD Distinct
%PK,
[%Date field],
%Key0
Resident Tab1;
Concatenate
LOAD Distinct
%PK,
[%Date field],
%Key1
Resident Tab2;
DROP Fields %PK, [%Date field] From Tab1, Tab2;
Calendar:
LOAD Distinct [%Date field] Resident LinkTable;
Left Join
LOAD [%Date field], ( Year([%Date field]) & '-' & Month([%Date field]) ) as [YearMonth date] Resident Calendar;
use [YearMonth date] field as your chart dimension
use the below expression:
Sum( {< %Rejected = {'1'} >} Distinct [Tab1.Candidate No] )
/
Count( Distinct [Tab1.Candidate No] )
Hi Tauceef,
I believe you need to choose to plot the % of rejected candidates either by OfferMade or OfferClosed (I assume you have field like this in your data > date that signifies when was the offer closed, if it was rejected or accepted).
You can use techniques like "Canonical Date" calendar (Canonical Date) which will allow you to plot both counts (offered as well as rejected) on a single Month-Year dimension. But if you need to plot the ratio, it will have to be just by one of the available dates.
Regards,
Radovan