Treat null as 0 in LINST_M calculation

[Anonymous]

Hi Qlikkers!

I am trying to get the LINST_M of the count of unique values across a set of buckets. There are cases where a bucket will be empty

There are 3 ways to handle the empty bucket. Here they are using exel for simplification

1. Ignore the missing bucket.

This is what Qlik gives as the equation when adding the trendline on a bar chart unless I tell it to show all dimensions .  It's not what I want as it ignores bucket 4

    

2. Include the bucket with no values, but with a value of null. Not what I want either.

3. Include the the bucket with a value of 0.

I want the third option. Because the expression is a count, it should treat nothing (null) as zero. I can get the correct value by displaying the expression when adding the trend line, but I cannot get the same value as an expression.

This expression gives me the second option

=LINEST_M(
    aggr(
        count({<AnalysisSeparator = {'x'}>} UniqueID), Salesman, Bucket
    ), Bucket
)

Can anyone assist in getting the LINST_M calculation to treat the empty bucket as 0?

I tried putting in a rangesum, an if, a len, but no joy. (I probably just have a parenthesis in the wrong place))

A sample QVW is attached which shows the problem for salesperson Fred.

Running Qlikview 11.

Cheers,

[#2]

Hi Christopher,

Did you manage to find a solition?

[sunny_talwar]

tomsharkbelarus‌ -

One solution in the above sample case could be to use something like this

=LINEST_M(Aggr(RangeSum(Count({<AnalysisSeparator = {'x'}>}UniqueID), Avg({<AnalysisSeparator = {'y'}>} 0)), Salesman, Bucket), Bucket)

Now in the above case we got lucky that there is a Bucket available for AnalysisSeparator = y, but if you don't have that, you will unfortunately modify your script to get at least a row for Salesman and Bucket. Because if the row doesn't exists, Aggr() won't pick it.

[#4]

Thank you, Sunny,

In my particular case I have even not the pairs (y=transactions, x=dates) at all, but I need them to build a valid trend in QlikSense chart and table for each and every article of goods.

There is another old solution, I found here Re: linest_m problem

I don't like this but I add "zero transactions" in script for filling gaps: several hundred articles of goods at several hundred days in master calendar.....not a lot for Qlik.

Kind regards.