Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

belias1980
New Contributor II

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

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

1 Solution

Accepted Solutions
MVP
MVP

Re: ApplyMap Question on Dates

Modify the load script:

LOAD

[PCT] AS [v_PCT_Charges_Sales_Analyst.PCT],

  RepNo,

  Date([GroupSymbol] ) AS GroupSymbol,

  PCTDeposit;

SQL SELECT "PCT", ...

JOIN

LOAD

  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)

6 Replies
MVP
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?

belias1980
New Contributor II

Re: ApplyMap Question on Dates

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

MVP
MVP

Re: ApplyMap Question on Dates

Modify the load script:

LOAD

[PCT] AS [v_PCT_Charges_Sales_Analyst.PCT],

  RepNo,

  Date([GroupSymbol] ) AS GroupSymbol,

  PCTDeposit;

SQL SELECT "PCT", ...

JOIN

LOAD

  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)

belias1980
New Contributor II

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

MVP
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:

Mapping LOAD RepNo, RepNo, RepName

FROM ...;

LOAD [PCT] AS [v_PCT_Charges_Sales_Analyst.PCT],

  RepNo,

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

  Date([GroupSymbol] ) AS GroupSymbol,

belias1980
New Contributor II

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?