Skip to main content

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
chrissy
Contributor II
Contributor II

Pivot Table - RangeSum for Dimensionality() 1

Desired Outcome:  Display a total at the Team level for all SalesTypes for the first column/Forecasted Leads to Close.

Issue:  Normally Qlik would display the total, however, in my case I have different ConversionRates per SalesType, therefore, Qlik cannot calculate the total.

Screenshot 2022-09-15 132605.png

Sample calculation:

 

(Count({<Last4Weeks = {'1'}>} OpportunityCount) / 4) 
* (Sum({<Status = {'New Client'}, Last4Weeks = {'1'}>} OpportunityCount) / Sum({<Last4Weeks = {'1'}>} OpportunityCount)) 
* ConversionRate

 

Calculation reads, over the last 4 weeks, sum the leads then divide by 4 to get an average per week.  Then multiply that by the percentage of New Client opportunities.  Then multiply that by the ConversionRate to determine a forecasted lead count to be won.

5 leads per week * 86% are New Clients leads * 65% Conversion Rate = ~3 New Client leads will convert to the next stage in the lead life cycle.

My specific issue is with the ConversionRate.  There is no aggregation on the ConversionRate (min, max, sum).  If I do Max, a total displays but it isn't the correct value.  Same holds true for the others.  

So I started going down the path of RangeSum().

 

=If(Dimensionality() = 1,
RangeSum(Below(Column(1)), 1, NoOfRows()),
// Other calculation here //
)

 

I have tried it with Aggr and other combinations. 

Does anyone have an insight on how to properly count the SalesTypes and display the count in the Team level?

I can provide the inline loads and calculations if needed.  

Thanks in advance!!

Chrissy

Labels (2)
1 Solution

Accepted Solutions
marcus_sommer
MVP & Luminary
MVP & Luminary

All relevant dimension must be added within the aggr() - at least the lowest level on which the calculation needs to be performed. Beside this it could be that you don't only need a set analysis within the inner aggregation else also within the outer aggregation and further also an optional TOTAL statement - means something like:

sum({< OuterSet >} TOTAL <Dim, Dim2> aggr(count({< InnerSet >} Field), Dim3, Dim4))

Depending on your data + data-model and the requirements to build the UI view it might even be to wrap the aggr() with another aggr() and/or to query the dimensionality() to branch into different different calculations. Yes, such approaches could become quite complex and therefore I suggest to consider in any parts of the logic could be transferred into the script to simplify the UI.

- Marcus

View solution in original post

6 Replies
Ruhulessin
Partner - Contributor III
Partner - Contributor III

Hi @chrissy,

Can you provide an app with the inline loads and the pivot with your calculations?
I will take a look at it.

-Ruben

I have never done it, so I think I can.
marcus_sommer
MVP & Luminary
MVP & Luminary

Aggr() should be the right approach - means something like:

sum(aggr(YourExpression, Dim1, Dim2))

whereby Dim1, Dim2 are placeholder for the needed dimensionality in which context the calculation should be happens. Quite often it are the dimensions used within the chart but sometimes it may even another/further ones like on a lower granularity and/or including any selections.

- Marcus 

chrissy
Contributor II
Contributor II
Author

@Ruhulessin -

Thank you for your assistance.  I don't have access to grab the actual Qlik file but here is the script.

SalesLeads:
Load *
Inline [
    Team, SalesType, LeadCount, Status, Last4Weeks
    Team 1, Outbound Sales, 250, New Client, 1
    Team 1, Inbound Sales, 25, New Client, 1
    Team 1, Inbound Sales, 50, Add Ons, 1
    Team 1, Renewals, 24, Renewals, 1
    Team 2, Outbound Sales, 163, New Client, 1
    Team 2, Inbound Sales, 36, New Client, 1
    Team 2, Inbound Sales, 42, Add Ons, 1
    Team 2, Renewals, 10, Renewals, 1
    Team 2, Add Ons, 15, Renewals, 1
];

Left Join (SalesLeads)
Load *
Inline [
    SalesType, ConversionRate
    Outbound Sales, .25
    Inbound Sales, .45
    Renewals, .8
    Add Ons, .95
];

 

Then it is a pivot table with Team and SalesType as the dimensions.  I have since dumbed down the calculation to 'Count(LeadCount) * ConversionRate'.  The issue is the ConversionRate not being aggregated.  So it doesn't matter too much what the first part of the calculation is.

Appreciate the assistance!!

chrissy
Contributor II
Contributor II
Author

@marcus_sommer -

Thanks for your suggestion.  The 'YourExpression' is where I am seeing the issue.  Part of the set analysis isn't in a Sum, Max, etc.  Therefore, it doesn't work at the top dimension.

ConversionRate is a multiplier per SalesType (lowest level) and not by Team (highest level).

Thanks for any additional insight.

marcus_sommer
MVP & Luminary
MVP & Luminary

All relevant dimension must be added within the aggr() - at least the lowest level on which the calculation needs to be performed. Beside this it could be that you don't only need a set analysis within the inner aggregation else also within the outer aggregation and further also an optional TOTAL statement - means something like:

sum({< OuterSet >} TOTAL <Dim, Dim2> aggr(count({< InnerSet >} Field), Dim3, Dim4))

Depending on your data + data-model and the requirements to build the UI view it might even be to wrap the aggr() with another aggr() and/or to query the dimensionality() to branch into different different calculations. Yes, such approaches could become quite complex and therefore I suggest to consider in any parts of the logic could be transferred into the script to simplify the UI.

- Marcus

chrissy
Contributor II
Contributor II
Author

Thank you for your assistance.  This solution worked great!