6 Replies Latest reply: Sep 7, 2017 5:03 PM by Basel Elias RSS

    ApplyMap Question on Dates

    Basel Elias

      Hello All!

       

      I have a dumb question that you would be able to help me. This can easily be done outside of Qlik Sense.

      But its not an easy concept for me to solve for and I've been attempting to solve for this now a few weeks.

       

      So i have two views that i had the DBA create for me and each independently show accurate data per sales rep.

      The sales rep is the field we want data rolled up on.

       

      The tables layouts are:

       

      The first table PCT Charges

          Columns:

                    PCT = 1(this can be ignored, no relation to the data)

                    RepNo = this is what i eventually want the data to group by in a pivot

                    GroupSymbol = This is a roll up of the date of transaction to the first of the month of that transaction

                    PCTDeposit = is a commission able deal that ranges from 0 to any decimal number

       

      PCTRepNoGroupSymbolPCTDeposit
      12149/1/20170.72

       

      The second table is TotalQualifying Sales

          Columns:

                    RepNo = this is what i eventually want the data to group by in a pivot

                    GroupSymbol = This is a roll up of the date of transaction to the first of the month of that transaction

                    TotalQualifying = Total Deals that sales rep has including PCTDeposits. So in the back of the view it is:                TotalsDealsforMonth+PCTDeposit = TotalQualifying

       

      RepNoGroupSymboltotalQualifying
      2149/1/201710.72

       

       

      What management would like to see is a run down of every rep on each row with a group by GroupSymbol.

         

      9/1/2010(GroupSymbol Union)
      RepTotal DealsPCT DealsTotalQualifying Deals
      214 = (PCTDeposit - TotalQualifying) = 10.000.7210.72

       

       

      Is this possible? if so? any assistance is greatly appreciated~

       

       

      The load script is:

       

      [v_PCT_Charges_Sales_Analyst]:

      LOAD

      [PCT] AS [v_PCT_Charges_Sales_Analyst.PCT],

      [RepNo] AS [v_PCT_Charges_Sales_Analyst.RepNo],

      Date([GroupSymbol] ) AS [GroupSymbol],

      [PCTDeposit] AS [PCTDeposit];

      SQL SELECT "PCT",

      "RepNo",

      "GroupSymbol",

      "PCTDeposit"

      FROM "Qlik"."dbo"."v_PCT_Charges_Sales_Analyst";

       

       

      [v_totalQualifying_Sales_Analyst]:

      LOAD

      [RepNo] AS [v_totalQualifying_Sales_Analyst.RepNo],

      [GroupSymbol] AS [GroupSymbol],

      [totalQualifying] AS [totalQualifying];

      SQL SELECT "RepNo",

      "GroupSymbol",

      "totalQualifying"

      FROM "Qlik"."dbo"."v_totalQualifying_Sales_Analyst";

       

       

      [autoCalendar]:

        DECLARE FIELD DEFINITION Tagged ('$date')

      FIELDS

        Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$axis', '$year'),

        Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged ('$quarter', '$cyclic'),

        Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$yearquarter', '$qualified'),

        Dual('Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [_YearQuarter] Tagged ('$yearquarter', '$hidden', '$simplified'),

        Month($1) AS [Month] Tagged ('$month', '$cyclic'),

        Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth', '$qualified'),

        Dual(Month($1), monthstart($1)) AS [_YearMonth] Tagged ('$axis', '$yearmonth', '$simplified', '$hidden'),

        Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber', '$cyclic'),

        Date(Floor($1)) AS [Date] Tagged ('$axis', '$date', '$qualified'),

        Date(Floor($1), 'D') AS [_Date] Tagged ('$axis', '$date', '$hidden', '$simplified');

       

       

      DERIVE FIELDS FROM FIELDS [GroupSymbol] USING [autoCalendar] ;