Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Do More with Qlik - for Beginners and Beyond, Topic: Qlik Replicate on January 21, 2PM EST. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor III
Contributor III

Select the 1% of the most expensive population

Hi,

I try to select the 1% of the most expensive population, I have made a graph of indicators that show how much the percentage of the cost consumes and a pareto table that accumulates the consumption of patients from higher to lower but I have tried to create a dimension that I classify these accumulated percentages of the pareto table by percentiles to filter.

If someone gives me a light I will be very grateful

7 Replies
Partner
Partner

Can u please share what u have?

some data?

It would be much easier to imagine a solution when we know how the data is constructed/calculated

Thanks

Contributor III
Contributor III

Hi Omar,

My data is structurate for servises then i have to aggregate the data for paciente, like that aggr(Sum(Costo),Paciente)

Partner
Partner

Can u dos thing like :

here's what I have:

CountryMeasure

and here's what I want to do : ...

?

Specialist II
Specialist II

Have you tried looking at this?  It seems like it would set you off in the right direction.  And don't forget to give us some example data to help you with.  Omar is really, really good and helpful if you give him something to work with.

Recipe for a Pareto Analysis

Partner
Partner

STill don't know what u want to do; but I'll be trying to imagine that u have this and u want a pareto :

1) I'll add a rank field in my script

t:

load *  Inline [

Customer, Expense

1,100

2,50

3,300

4,40

5,50

6,600

7,10

8,90

9,100

10,250

];

t0:

load Customer, sum(Expense) as sum Resident t Group by Customer ;

NoConcatenate

load * ,RowNo() as Rank;

load Customer Resident t0 Order by sum desc;


Drop table t0;


2)
Create a bar chart

as dimension : Customer

as measure:

aggr(

rangesum(Above(

Sum({<Customer>}Expense)

, 0 , RowNo()))

,Rank)

sort it with expression : Rank (Ascend)

color it by expression:

if(aggr(

rangesum(Above(

Sum({<Customer>}Expense)

, 0 , RowNo()))

,Rank) / sum(total {<Customer>} Expense) <=0.95,red())


Result:

Capture.PNG

Now as a filter, create it as follow:

=aggr(if(aggr(

rangesum(Above(

Sum({<Customer>}Expense)

, 0 , RowNo()))

,Rank) / sum(total{<Customer>} Expense) <=0.95,'1'),Customer)


Result:

Capture.PNG

Partner
Partner

Thanks Justin

Contributor III
Contributor III

Hi,

I have built the pareto with internal variables in the table, and I want to be able to select the highest percentile or a variable that identifies me as 'Pareto Paciente' so that it can be identified as percentiles and be able to filter the 1% of the most expensive patients

PARETO.png

The functions of each variable is:

Numero: this variable ranks patients

RowNO()

Paciente: This variable identify

[CONTRATO_AFIL]

Costo: This variable sum the cost

Sum([Autorizaciones.COSTO_SERVICIO])

Pareto Pesos: this variable is the weight of the cost

sum([Autorizaciones.COSTO_SERVICIO])/sum(total([Autorizaciones.COSTO_SERVICIO]))

Pareto Paciente: this variable is the weight of the pacient

RowNo()/Count(total(Aggr(RowNo(), [CONTRATO_AFIL])))

Pesos Acumulado: this variable is the weight of the accumulated pacient, this variable tells me how far the percentage of the patient is

Costo+IF(isnull(above(Costo)),0,above([Pesos Acumulado]))

Acumulado: this variable is the weight of the accumulated cost

[Pareto Pesos]+IF(isnull(above([Pareto Pesos])),0,above(Acumulado))