# ApplyMap Question on Dates

**Basel Elias**Sep 5, 2017 9:38 PM

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

PCT | RepNo | GroupSymbol | PCTDeposit |

1 | 214 | 9/1/2017 | 0.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**

RepNo | GroupSymbol | totalQualifying |

214 | 9/1/2017 | 10.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) | |||

Rep | Total Deals | PCT Deals | TotalQualifying Deals |

214 | = (PCTDeposit - TotalQualifying) = 10.00 | 0.72 | 10.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] ;