topic Re: Average of (Fractile function) in QlikView

Average of (Fractile function)

mario-sarkis — Tue, 25 Aug 2015 09:15:07 GMT

No of Clients No of Trasactions

10 100
20 200
30 300
40 400
50 500
60 600
70 700
80 800

My Question as we can see the total nober of transaction in 3600

If I use the function fractile(Trasactions,0.9) this will give me the number of client that did the 90% of transactions …

I need to find lets say the average number of transactions per Clients who did 90% of these transactions.

Please help Anyone ?

dreamer4

settu_periasamy

sunindia

Re: Average of (Fractile function)

sunny_talwar — Tue, 25 Aug 2015 09:52:24 GMT

When I use the below formula, I get the output as 730.

=Fractile([No of Trasactions], 0.90)

What should the average be for customers??? Is it going to be the average of all the customer with No of Transactions below 730??

Re: Average of (Fractile function)

mario-sarkis — Tue, 25 Aug 2015 10:36:40 GMT

hi sunindia yes i need the average of transactions by customer with the condition <=730 (excluding the 10 Percent rest)

Can you help it needs an aggr fucntion ?

Re: Average of (Fractile function)

sunny_talwar — Tue, 25 Aug 2015 10:39:27 GMT

May be this:

=Avg({<[No of Trasactions] = {"<= $(=Fractile([No of Trasactions], 0.90))"}>}[No of Trasactions])

Gives you the average of 100, 200, 300, 400, 500, 600 and 700 = 400

Re: Average of (Fractile function)

mario-sarkis — Tue, 25 Aug 2015 12:02:09 GMT

ID	Segment	Amount
1	Medium	1000
2	Small	300
3	Small	500
4	Medium	2000
5	Small	100
6	Small	200
7	Medium	500
8	Small	600
9	Medium	3000
10	Medium	2500
11	small	50

ID	Segment	Amount	Total Nb of IDs	90% of 5	Avg Amount
7	Medium	500	5	4.5	1500
1	Medium	1000
4	Medium	2000
10	Medium	2500
9	Medium	3000

ID	Segment	Amount	Total Nb of IDs	90% of 5	Avg Amount
11	small	50	6	5.4	290
8	Small	600
6	Small	200
5	Small	100
3	Small	500
2	Small	300

Output:

Output:	Avg 90%	Median	Avg	Min	Max
Medium	1500	2000	1800	500	3000
Small	290	250	291.6666667	50	600

sunindia sorry for disturbing you , this is exactly wut i want as an output a Pivot Table as it show above and the first table is my row Data , as you can see i need to sort it Decreasing by segment and the calculate in a set analysis i dont want to change my row data in the script

Hope you can help thank you my friend

Re: Average of (Fractile function)

mario-sarkis — Tue, 25 Aug 2015 12:23:33 GMT

Average 90% is calculated :

for medium lets say is calculate it as (500+1000+2000+2500)/4=1500

for Small (50+100+200+300+500)/5=230

sunindia

Re: Average of (Fractile function)

sunny_talwar — Tue, 25 Aug 2015 14:25:09 GMT

this?

Avg 90%Expression:

=Avg(If(Aggr(Fractile(TOTAL <Segment> Amount, 0.90) >= Amount, Segment, Amount), Amount))