Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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] ;
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)
GroupSymbol is 9/1/2017 in one table and 9/1/2010? is this a typo or is this how the data looks?
Yes, i fixed it.. sorry that would be where the union would go.
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)
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
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,
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?