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

    Monthly trend graph for 2 date dimensions

    Tauceef Sheikh

      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

        • Re: Monthly trend graph for 2 date dimensions
          Andrea Gigliotti

          maybe this:

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

          /

          Count( Distinct SONo )

            • Re: Monthly trend graph for 2 date dimensions
              Tauceef Sheikh

              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

                • Re: Monthly trend graph for 2 date dimensions
                  yusuuf rawat

                  Try

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

                  • Re: Monthly trend graph for 2 date dimensions
                    Andrea Gigliotti

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

                • Re: Monthly trend graph for 2 date dimensions
                  Radovan Oresky

                  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