# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
New Contributor III

## ApplyMap Question on Dates

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~

[v_PCT_Charges_Sales_Analyst]:

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

[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] ;

Tags (3)
1 Solution

Accepted Solutions
Highlighted
MVP

## Re: ApplyMap Question on Dates

[PCT] AS [v_PCT_Charges_Sales_Analyst.PCT],

RepNo,

Date([GroupSymbol] ) AS GroupSymbol,

PCTDeposit;

SQL SELECT "PCT", ...

JOIN

RepNo,

Date(GroupSymbol) AS GroupSymbol,

totalQualifying as TotalQualifying;

SQL SELECT "RepNo", ...

Then in a table, use RepNo as Dimension (and perhaps GroupSymbol), and the following expressions

 Total Deals: =Sum(RangeSum(TotalQualifying, -PCTDeposit)) PCT Deals: =Sum(PCTDeposit)

Total Qualifying Deals: =Sum(TotalQualifying)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
6 Replies
Highlighted
MVP

## Re: ApplyMap Question on Dates

GroupSymbol is 9/1/2017 in one table and 9/1/2010? is this a typo or is this how the data looks?

Highlighted
New Contributor III

## Re: ApplyMap Question on Dates

Yes, i fixed it.. sorry that would be where the union would go.

Highlighted
MVP

## Re: ApplyMap Question on Dates

[PCT] AS [v_PCT_Charges_Sales_Analyst.PCT],

RepNo,

Date([GroupSymbol] ) AS GroupSymbol,

PCTDeposit;

SQL SELECT "PCT", ...

JOIN

RepNo,

Date(GroupSymbol) AS GroupSymbol,

totalQualifying as TotalQualifying;

SQL SELECT "RepNo", ...

Then in a table, use RepNo as Dimension (and perhaps GroupSymbol), and the following expressions

 Total Deals: =Sum(RangeSum(TotalQualifying, -PCTDeposit)) PCT Deals: =Sum(PCTDeposit)

Total Qualifying Deals: =Sum(TotalQualifying)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Highlighted
New Contributor III

## Re: ApplyMap Question on Dates

Thank you very much!

You solved a huge problem for me, i appreciate it.

One last thing - if i wanted to add another table to that same script load that had the Rep names associated with Rep Numbers, how would i do the join there?

The table association doesn't give me the ability to associate it.

So the new table is:

RepNo, RepName

Thanks

Highlighted
MVP

## Re: ApplyMap Question on Dates

If you just need to add the Rep Names, then I suggest that you load the RepNos and RepNames into a mapping table, wnd add with an ApplyMap during the first load.

MAP_RepNames:

FROM ...;

RepNo,

ApplyMap('MAP_RepNames', RepNo, 'Missing') as RepName,

Date([GroupSymbol] ) AS GroupSymbol,

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Highlighted
New Contributor III

## Re: ApplyMap Question on Dates

Thank you Jonathan,

I appreciate it, so if the exec's want to add additional information like business channel and type of transactions by reps and those sit on different tables, would the best solution be to have a view that pulls all of this into one view?