topic Re: Decile analysis with filter in QlikView

Decile analysis with filter

eddy9657 — Fri, 25 Nov 2022 10:01:01 GMT

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.

Re: Decile analysis with filter

Vegar — Fri, 25 Nov 2022 05:43:34 GMT

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

Re: Decile analysis with filter

eddy9657 — Fri, 25 Nov 2022 06:19:19 GMT

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

Re: Decile analysis with filter

Vegar — Fri, 25 Nov 2022 07:32:27 GMT

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.

Re: Decile analysis with filter

eddy9657 — Fri, 25 Nov 2022 07:50:35 GMT

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

Re: Decile analysis with filter

Vegar — Fri, 25 Nov 2022 15:11:21 GMT

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)

Re: Decile analysis with filter

eddy9657 — Mon, 28 Nov 2022 02:02:01 GMT

Hi Vegar, thanks so much for your help😉