Monthly trend graph for 2 date dimensions

[tauceef9]

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

[agigliotti]

maybe this:

Count( {< [Offer Rejected Date] = {"*"} >}  Distinct SONo )

/

Count( Distinct SONo )

[tauceef9]

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

[vvvvvvizard]

Try

Date(Date3((Year(Date)*100)+Month(Date),'YYYYMM'),'MMM-YY')

[agigliotti]

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] )

[RadovanOresky · Accepted solution]

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