topic Re: Consider 0 sales for fractile calculation in App Development

Consider 0 sales for fractile calculation

TDQlik — Tue, 06 Feb 2024 15:52:22 GMT

Hi,

We have an interesting problem. We have a sales table and a stock table.

We'd like to calculate the top 20% fractile over the Average number of units sold per weeks selected.

However, using =Fractile(total Aggr([Average Units per Week], Product), 0.8) does not yield the right result and that's because the 0 sold items aren't shown. But these items do have stock on hand.

This most likely happens because the items not sold do not have a transaction date and therefore when weeks are selected, they disappear. How can we get around this?

Attached an Excel file with example data. (Expected to have) is what we want :). Top 20% fractile should be 1,9 and not 2,6.

Thanks!!!

Re: Consider 0 sales for fractile calculation

Dataintellinalytics — Tue, 05 Mar 2024 05:51:20 GMT

PFA, When I tried in Qlik I got 1.9 not 2.6.

Re: Consider 0 sales for fractile calculation

TDQlik — Tue, 05 Mar 2024 13:29:22 GMT

Hi Dataintellinalytics,

Thanks for your effort and time spent on it.

I'm fairly certain it worked in Qlik Sense because the 0s are existing as rows in the fact in the Excel I provided. A true replication would be to have Products 19-25 exist in the Stock fact table but not in the Sales fact table, then put those together and try again.

Our issue is indeed that the 0 does not exist as a row, it is Qlik's auto-population of the row in the table visualization because it has Stock, but no Sales.

Sadly our issue still persists today. Thank you for your reply though!

Re: Consider 0 sales for fractile calculation

marcus_sommer — Thu, 07 Mar 2024 11:42:05 GMT

The simplest approach is to create the missing data as NULL/ZERO records. Yes, it will need some efforts within the data-model then everything else will be much more expensive.

Re: Consider 0 sales for fractile calculation

Dataintellinalytics — Thu, 07 Mar 2024 15:21:41 GMT

Understood the issue,

one quick solution would be to create a temp table in script by doing a left join Stock with Sales with all required columns, solely for the purpose of doing Fractile in UI.

Meanwhile am looking for other possible solutions.

Re: Consider 0 sales for fractile calculation

Dataintellinalytics — Mon, 11 Mar 2024 05:57:48 GMT

With this expression Sum([Avg Units Per Week]) + Sum({$<Product={"*"}>} 0) we can capture Product with no sales but unfortunately couldn't make it work along with Fractile function.

variable -> Test = Sum([Avg Units Per Week]) + Sum({$<Product={"*"}>} 0)
Chart Exp -> Fractile( Aggr(nodistinct $(Test),Product), 0.8)

Re: Consider 0 sales for fractile calculation

TDQlik — Mon, 11 Mar 2024 07:53:14 GMT

Thanks a lot Dataintellinalytics!

I'll give that a shot when I'm able to and report back!