Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

tauceef9
Contributor

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 NoCandidate NoIndent StatusOffer Rejected DateDeclined reasonOfferMade
7310353Closed21-Oct-10Not Interested24-Aug-10
7310354Closed22-Oct-10Tech Rejection24-Sep-10
7410355Closed23-Nov-10Not Interested25-Oct-10
7510356Closed24-Oct-10Tech Rejection26-Sep-10

I want do count the candidates who have rejected the offer vs offers made

How do I solve this?

Regards,

Tauceef

1 Solution

Accepted Solutions
radoresky
Contributor

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,

Radovan

5 Replies
agigliotti
Honored Contributor II

Re: Monthly trend graph for 2 date dimensions

maybe this:

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

/

Count( Distinct SONo )

tauceef9
Contributor

Re: Monthly trend graph for 2 date dimensions

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
Contributor III

Re: Monthly trend graph for 2 date dimensions

Try

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

agigliotti
Honored Contributor II

Re: Monthly trend graph for 2 date dimensions

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

radoresky
Contributor

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,

Radovan

Community Browser