Decile analysis with filter

[eddy9657]

Hi All,

I am using below logic 

=Aggr(
Ceil(Rank(Sum({1}Profit),4,1) / count({1}DISTINCT TOTAL Customer), 0.1) * 10
, Customer)

from https://community.qlik.com/t5/QlikView-App-Dev/Decile-analysis-with-filter/m-p/2006496#M1221552

By using this logic
When I select 10% it will continue the divide 10 part from the 10% of customer. The user can't identify they already select 10%. >> This problem has been solved.

However ,  I need select other filter and create alternative status(1,2,3)  .The user can select different filter for example Country > then choose  10% 50% 70% from 3 different Decile filter by using below expression.

Aggr(
Ceil(Rank(Sum({$}Profit),4,1) / count({$}DISTINCT TOTAL Customer), 0.1) * 10
, Customer) 

If I select filter for example Country = US, the decile calculation not base on US. the calculation base on the source data (All data) then filter US.
But I want the Calculation base on the filter data  = US. 

If the alternative status =default or inherited  ,

Although Calculation base on the filter data  = US ,when I select 20% it will continue the divide 10 part from the 20% of customer. The user can't identify they already select 20%.

 

 

 

Can anyone help  ? Thanks.

[Vegar]

Your expression looks OK. 

It this an exact copy of your expression or could it be that you still are using  a full set {1} in your expression. 

Ps: you don't  need to explicit write {$} in aggregation functions, {$} is assumed by default 

[eddy9657]

{1} : Problem :  Calculation base on the full data no matter what other filter select 

{$}  : Problem : When I select 10% it will continue divide 10 part from the 10% of customer. The user can't identify they already select 10%.

So {$} or {1} still can't solve my problem.

I want the calculation can affect by other filter and when I select decile filter it will not continue the divide 10 part from the 10% of customer.

[Vegar]

I think you need to be more specific in order to get further help.

Is it possible for you to create a sample data set and application that pinpoints your problem. 

I think a mockup dataset with the following fields should be enough:

• Customer
• Country
• Status
• Profit

In that sample app we will se what you get and can then try to help you.

[eddy9657]

Sorry Vegar.  My company only have online version on Qlik sense. 

3 different Decile filter on the same page. Let the User choose 3 Decile to compare the transaction record

By using this expression as filter 

Aggr(
Ceil(Rank(Sum({$}Profit),4,1) / count({$}DISTINCT TOTAL Customer), 0.1) * 10
, Customer) 

When I select 20% it will continue the divide 10 part from the 20% of customer. The user can't identify they already select 20%.

 Please find the simple data for your reference

[Vegar · Accepted solution]

The excel and explanation was helpful. I think I understand your issue.

Try this:

=Aggr(
   Ceil(Rank(Sum({$<Customer>}Profit),4,1)
     / count({$<Customer>}DISTINCT TOTAL Customer)
     , 0.1) * 10
   , Customer)

[eddy9657]

Hi Vegar, thanks so much for your help😉