5 Replies Latest reply: Dec 19, 2017 9:59 AM by Radovan Oresky

# Monthly trend graph for 2 date dimensions

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

• ###### Re: Monthly trend graph for 2 date dimensions

maybe this:

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

/

Count( Distinct SONo )

• ###### Re: Monthly trend graph for 2 date dimensions

Hi Andrea,

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

• ###### Re: Monthly trend graph for 2 date dimensions

Try

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

• ###### Re: Monthly trend graph for 2 date dimensions

try this script:

QUALIFY '*';

UNQUALIFY '%*';

Tab1:

AutoNumber(%PK, [%Date field]) as %Key0;

[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:

AutoNumber(%PK, [%Date field]) as %Key1;

[SO No] as %PK,

[Candidate No],

[Indent Status],

[Declined reason],

From YourTable;

%PK,

[%Date field],

%Key0

Resident Tab1;

Concatenate

%PK,

[%Date field],

%Key1

Resident Tab2;

DROP Fields %PK, [%Date field] From Tab1, Tab2;

Calendar:

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

• ###### Re: Monthly trend graph for 2 date dimensions

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,