topic Re: Fractile function error in QlikView

Fractile function error

satishkurra — Sat, 26 May 2018 13:18:13 GMT

Attached is the sample data and app.

It has 2 expressions

1. Revenue/Hours

2. Hours

I'm trying to display in scatter plot using dimension ID

Also the current logic is to display ID's between 5 and 95 percentile. Hence i used FRACTILE function.

However when i select random data, attached is the image and highlighted in RED, it is saying NO DATA TO DISPLAY.

Now i'm trying to get to the state of achieving between (StdDev * -0.5 + Mean) and (StdDev * 0.5 + Mean)

Please help me on the same

Thanks

Satish

Re: Fractile function error

Neymar_Jr — Sat, 26 May 2018 14:46:12 GMT

Hi Satish,

PFA

Thanks,

Re: Fractile function error

sunny_talwar — Sun, 27 May 2018 00:38:37 GMT

Another option

Fee Rate $

(Avg({<RecordID =

{"=Avg([Fee Revenue]) >= Fractile({1} TOTAL Aggr(Avg({1} [Fee Revenue]), RecordID), 0.05) and Avg([Fee Revenue]) <= Fractile({1} TOTAL Aggr(Avg({1} [Fee Revenue]), RecordID), 0.95)"}

>}[Fee Revenue]))

(Avg({<RecordID =

{"=Avg([Engagement Hours]) >= Fractile({1} TOTAL Aggr(Avg({1} [Engagement Hours]), RecordID), 0.05) and Avg([Engagement Hours]) <= Fractile({1} TOTAL Aggr(Avg({1} [Engagement Hours]), RecordID), 0.95)"}

>}[Engagement Hours]))

Hours Manged

Sum({<RecordID =

{"=Sum([Engagement Hours]) >= Fractile({1} TOTAL Aggr(Sum({1} [Engagement Hours]), RecordID), 0.05) and Sum([Engagement Hours]) <= Fractile({1} TOTAL Aggr(Sum({1} [Engagement Hours]), RecordID), 0.95)"}

>}[Engagement Hours])

Re: Fractile function error

satishkurra — Sun, 27 May 2018 04:28:49 GMT

Thanks Sunny and Rajesh

Instead of restricting ID's between 5 and 95% percentile, can we restrict between

(StdDev * -0.5 + Mean) and (StdDev * 0.5 + Mean)

Thanks

Satish

Re: Fractile function error

sunny_talwar — Sun, 27 May 2018 10:48:48 GMT

This?

(Avg({<RecordID =

{"=Avg([Fee Revenue]) >= Avg({1} TOTAL Aggr(Avg({1} [Fee Revenue]), RecordID)) - (0.5 * Stdev({1} TOTAL Aggr(Avg({1} [Fee Revenue]), RecordID))) and

Avg([Fee Revenue]) <= Avg({1} TOTAL Aggr(Avg({1} [Fee Revenue]), RecordID)) + (0.5 * Stdev({1} TOTAL Aggr(Avg({1} [Fee Revenue]), RecordID)))"}

>}[Fee Revenue]))

(Avg({<RecordID =

{"=Avg([Engagement Hours]) >= Avg({1} TOTAL Aggr(Avg({1} [Engagement Hours]), RecordID)) - (0.5 * Stdev({1} TOTAL Aggr(Avg({1} [Engagement Hours]), RecordID))) and

Avg([Engagement Hours]) <= Avg({1} TOTAL Aggr(Avg({1} [Engagement Hours]), RecordID)) + (0.5 * Stdev({1} TOTAL Aggr(Avg({1} [Engagement Hours]), RecordID)))"}

>}[Engagement Hours]))

Sum({<RecordID =

{"=Avg([Engagement Hours]) >= Avg({1} TOTAL Aggr(Avg({1} [Engagement Hours]), RecordID)) - (0.5 * Stdev({1} TOTAL Aggr(Avg({1} [Engagement Hours]), RecordID))) and

Avg([Engagement Hours]) <= Avg({1} TOTAL Aggr(Avg({1} [Engagement Hours]), RecordID)) + (0.5 * Stdev({1} TOTAL Aggr(Avg({1} [Engagement Hours]), RecordID)))"}

>}[Engagement Hours])

Re: Fractile function error

satishkurra — Mon, 28 May 2018 15:50:11 GMT

Hi Sunny

Thanks for the logic.

I applied this logic to Revenue vs GM % and now i see Revenue values showing as Zero for few ID's

Example: ID 980 (Attached screenshot for the bubble 980 in chart)

Attaching the latest app, data and screenshot

Thanks

Satish !

Re: Fractile function error

sunny_talwar — Mon, 28 May 2018 16:26:08 GMT

I guess Gross Margin % is using Fee Revenue and Revenue is using Engagement Revenue.... you need to decide which confidence interval you want to go with... you seem to have a different RecordID picked based on different expression. I won't know what the final goal is until you explain it clearly.

Re: Fractile function error

satishkurra — Mon, 28 May 2018 17:01:44 GMT

‌HI Sunny

thanks for the response.

the goal is to distribute the bubbles evenly instead of making it close to axes where all bubbles sit close to axes.

FFee Rate chart is distributed evenly. But if I take gross margin vs revenue for few ids revenue is zero.

DDoes it make sense now?

thanks

satish

Re: Fractile function error

sunny_talwar — Mon, 28 May 2018 17:06:56 GMT

I am still not sure I understand... but try the attached

Re: Fractile function error

satishkurra — Mon, 28 May 2018 18:00:37 GMT

Hi Sunny

Thank you very much for this.

I implemented this change and waiting for feedback from client.

I will update you if any issues.

I'm closing this as correct answer as the results are satisfactory and correct

Thanks

Satish