How can I calculate, in QlikView, the regression coefficient in a graphic/table object using my not-so-simple data structure?

[mlarruda]

Let me explain my data structure. I have three main fields: LAWYER, PROCESS and DATE. My main interest is to study the quantity of processes by lawyer, by month. So, usually I would use the dimension Month(DATE) and the expression Count(Distinct PROCESS) to build a chart for a given lawyer.

However, there are lawyers with 0 processes in some month(s). So, I created an auxiliar field DAY in the script, by using:

Add

TempCalendary: 

LOAD
   Date($(vDateMin) + RowNo() - 1) as DAY,
   AUTOGENERATE 1 
WHILE $(vDateMin)+IterNo()-1 <= $(vDateMax); 

And changed my dimension and expression to, respectively, MonthName(DAY) and Count(DISTINCT IF(MonthName(DATE) = MonthName(DAY), PROCESS)) .

The chart worked but my final goal is to get the linear trend (regression linear coefficient) of the processes per month, for each lawyer. So, I made a graphic with dimension LAWYER and expression LINEST_M(Count(DISTINCT IF(MonthName(DATE) = MonthName(DAY), PROCESS)) , LAWYER) but it didn't worked. How can I make it work?

[Oleg_Troyansky]

Hi there,

It looks like you are trying to associate your DATE field with the DAY field manually, using the IF function. In QlikView, it's much better to use the associative logic that Qlik offers, and associate these fields directly.

- Generate the list of Dates the same way you did, only naming the field the same way as it's named in your fact table

- Then, add all calendar attributes to the generated Calendar Table - Month, YearMonth, Quarter, etc...

Due to the identical field names in the two tables, they will be linked automatically, and your Month field will be associated with the rest of the data.

Since you use QlikView, I'd like to recommend to you my book  QlikView Your Business - you can learn a lot of useful development techniques from it, starting from the basics and going quite deep into advanced material.

Cheers,

Oleg Troyansky

[mlarruda]

Thank you very much, Oleg. The chart worked but the table with dimension LAWYER and expression LINEST_M(Count(Distinct PROCESS), DATE) is still not working.

[hic]

You cannot have one aggregation function inside another aggregation function, unless you use Aggr().

So, the following is not allowed:
LINEST_M(Count(Distinct PROCESS), DATE)

But the following is:
LinEst_M(Aggr(Count(Distinct PROCESS),DATE),DATE)