topic Re: Fractile - QlikView in QlikView

Fractile - QlikView

satishkurra — Thu, 03 May 2018 16:59:11 GMT

I'm facing issue in displaying the chart for < 95 percentile values.

Attached is the data, i have for Record Id, Revenue. Gross Margin % is a calculated field in the expression (Gross Margin/Fee Revenue)

I also have expressions for Revenue and Gross Margin % as

Revenue:

Sum({<FiscalYear={'$(vMaxYear)'},Date={'<=$(vMaxDate1)'}, Fiscal_Month=>}[Revenue])

Gross Margin %:

Avg({<FiscalYear={'$(vMaxYear)'},Date={'<=$(vMaxDate1)'}, Fiscal_Month=>}[Gross Margin])

Avg({<FiscalYear={'$(vMaxYear)'},Date={'<=$(vMaxDate1)'}, Fiscal_Month=>}[Fee Revenue])

I need to display the chart only for < 95 percentile values.

Can someone help me on the same.

Re: Fractile - QlikView

satishkurra — Thu, 03 May 2018 20:24:39 GMT

Any one?

Re: Fractile - QlikView

satishkurra — Fri, 04 May 2018 01:30:56 GMT

Help

Re: Fractile - QlikView

sunny_talwar — Fri, 04 May 2018 02:03:16 GMT

What is the exact issue you are running into? Can you share a qvw app where you have been trying to make it work and failing? This might help us understand the issue better

Re: Fractile - QlikView

satishkurra — Fri, 04 May 2018 02:11:10 GMT

Hi Sunny

Attached is the data client shared and mentioned to display outliers which are less than 95th percentile values.

Does this make sense now?

Thanks

Satish

Re: Fractile - QlikView

sunny_talwar — Fri, 04 May 2018 02:26:04 GMT

I see the Excel attachment, but I am not sure how is this created and what is that you desire to see. That is why was asking for you to share the qvw file.

Re: Fractile - QlikView

satishkurra — Fri, 04 May 2018 02:45:32 GMT

Attached is the qvw.

If you see the scatter plot all the bubbles are at the same position

I would like to distribute by that it looks better.

Client mentioned display data only for < 95th Percentile and see how it looks like

Re: Fractile - QlikView

sunny_talwar — Fri, 04 May 2018 02:55:14 GMT

Looking for this?

Re: Fractile - QlikView

satishkurra — Fri, 04 May 2018 03:03:39 GMT

I see you supress null values

I attached another file and see sheet 1 with another scatter plot. This is not distributed

Re: Fractile - QlikView

sunny_talwar — Fri, 04 May 2018 03:05:49 GMT

I see the scatter

Are you seeing something else?

Re: Fractile - QlikView

satishkurra — Fri, 04 May 2018 03:08:46 GMT

Hi Sunny

I see scatter plot too.

All the bubbles are at the same position.

Is there a way to include values in chart with only < 95th percentile values?

Thanks

Satish

Re: Fractile - QlikView

sunny_talwar — Fri, 04 May 2018 03:09:56 GMT

Same position?

Re: Fractile - QlikView

satishkurra — Fri, 04 May 2018 03:34:53 GMT

I mean close to Zero. If we get the values of 95th percentile, may be we get better distribution across the chart?

Re: Fractile - QlikView

sunny_talwar — Fri, 04 May 2018 09:04:40 GMT

Assuming you want below 95% by Bookings $... try these two expression

Sum({<ID = {"=Sum([Bookings $]) <= Fractile(TOTAL Aggr(Sum([Bookings $]), ID), 0.95)"}>}[Bookings $])

Sum({<ID = {"=Sum([Bookings $]) <= Fractile(TOTAL Aggr(Sum([Bookings $]), ID), 0.95)"}>}[Pipeline $])

Re: Fractile - QlikView

satishkurra — Fri, 04 May 2018 13:26:19 GMT

Thanks Sunny.