Consider 0 sales for fractile calculation

[TDQlik]

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!!!

[Dataintellinalytics]

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

[TDQlik]

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!

[marcus_sommer]

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.

[Dataintellinalytics]

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.

[Dataintellinalytics]

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)

[TDQlik]

Thanks a lot Dataintellinalytics!

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