Pivot Table - RangeSum for Dimensionality() 1

[chrissy]

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.  

Thanks in advance!!

Chrissy

[Ruhulessin]

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

[marcus_sommer]

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]

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

@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 · Accepted solution]

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]

Thank you for your assistance.  This solution worked great!