# App Development

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save \$300 before February 6: REGISTER NOW!
cancel
Showing results for
Did you mean:
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.

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.

Chrissy

Labels (2)

• ### General Question

1 Solution

Accepted Solutions
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

6 Replies
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.
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

Contributor II
Author

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

``````SalesLeads:
Inline [
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
];

Inline [
SalesType, ConversionRate
Outbound Sales, .25
Inbound Sales, .45
Renewals, .8
];``````

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!!

Contributor II
Author

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).

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

Contributor II
Author

Thank you for your assistance.  This solution worked great!

Tags
Community Browser