topic Creating truncated reference lines in QlikView

Creating truncated reference lines

cgaleaQlik — Tue, 05 Jan 2021 23:09:22 GMT

Hi All,

I have created truncated reference lines for several items that I have plotted on a time series chart. Plotting each item separately (e.g. Fig 1 below) works OK but when I try to select all items the truncated reference line does not look right.

I have attached an example QlikView file and data file.

Appreciate any help you can provide.

Fig. 1

Fig. 2

Thank you,

Charles

Re: Creating truncated reference lines

mfchmielowski — Thu, 07 Jan 2021 11:16:55 GMT

Hi Charles.

I've modified yours expression to:

if(wildmatch(GetFieldSelections(Item), 'A*') and Date < '01 Jul 2020', 5, if(wildmatch(GetFieldSelections(Item), '*B*') and Date < '01 Jul 2020', 3, if(wildmatch(GetFieldSelections(Item), '*C*') and Date < '01 Jul 2020', 1, if(wildmatch(GetFieldSelections(Item), '*D*') and Date < '01 Jul 2020', 2.5, if(( match(GetFieldSelections(Item,','), 'A,B,C,D') or len(GetFieldSelections(Item)) < 1 ) and Date < '01 Jul 2020', 10, 0)))))

It's not perfect becease will work properly only when you select on Item listbox: one value, all values or leave it empty. Please read GetFieldSelections() documentation before work with wider set of items. Calculated 'Reference lines' should use agregating function like sum().

I suggest you to add reference value to script data, Ref_Lines_Data.xlsx as a values to sum. Will be easier to create a valid expression.

Re: Creating truncated reference lines

cgaleaQlik — Thu, 07 Jan 2021 21:53:35 GMT

Great, looks like it will help for now. Appreciate your help!!!