Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
jonathandienst
Partner - Champion III
Partner - Champion III

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)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

6 Replies
sunny_talwar

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

Anonymous
Not applicable
Author

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

jonathandienst
Partner - Champion III
Partner - Champion III

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)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Anonymous
Not applicable
Author

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

jonathandienst
Partner - Champion III
Partner - Champion III

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,

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Anonymous
Not applicable
Author

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?