Line chart with dynamic unknown number of expressions?

[andy]

Hi folks,

As been stated in this forum Qlikview's line chart is limited to only displaying 2 dimensions and 1 expressions or 1 dimension and several expressions. I have a case where I have a

- position lets say 1-100 on the x-axis

- a threshhold (which is not a straight line)

- datafiles with values connected to the values on the x-axis.

The amount of data-files can be any and I don't know the number of them. I want the x-axis to be 1-100 and then a new color for each line representing the threshold and the datafile values.

Then I need to create an expression for each datafile but how can that be done. (Please note it should be no aggregated values but the raw data.)

RIght now I removed the threshold line and added a second dimension being the filenames and that displays one unique line per data-file but I really want the threshold line to be in the chart too! (If it was a straight line it would be just a simple reference line)

Any help is apporeciiated

brgds

Andreas

[Gysbert_Wassenaar]

There's only one threshold line if I understand you correctly. If you know the threshold values for every position on the x-axis you could add it to the table with datafile values. That way you only need two dimensions and one expression.

[andy]

Hi,

Thanks for the answer. I realized that and tried it out, just concatenating the threshold data to the data-table and set the filenname= 'Threshold' but then I got stuck in the fact that there is a lot of other columns in the data-table for example timestamp, contractor, year, month etc etc and then if a selection is made in any of those the threshold line disappears. I don't want to outer join on all the columns since that will explode the data. Either I have to live with the fact that the threshold line is just there when there is no selection or a selection on x-axis. Or anyone have a hint?

If I didn't needed the raw data I could have ignored selections with set-analysis but now I don't know how to do.

/Andy

[Gysbert_Wassenaar]

Try something like this in your expression: if(filename='Threshold', sum({1} Value), sum(Value)). Basically, based on the filename you pick which expression to show.

Or maybe with a variable:

vSetMod: if(filename='Threshold', {1})

expression: sum( $(vSetMod) Value)

[andy]

Of course! Why didn't I think of that....

That should work but now I've spent 2 hours fiddling around with the expressions and I cannot get it to work the way I want.

I add a small example. There are 2 threshold lines with filenames Limit_2 and Limit_3.

The expression is

if(left(EQD.FileName,5)='Limit',sum({1} -EQD.DepthOfBurial),sum(DISTINCT -EQD.DepthOfBurial))

even though the 'Limits' disappear from the chart as soon as any selection is made in the data.

I want every single value being displayed in the chart with a separate color for each filename.

The reference lines (Limit_21 and Lmit_3) shall always be presented for the choosen x-axis values.

/Andy

[Gysbert_Wassenaar]

Yes, the selections are applied to the set, so if you select a value then the rest won't be in your chart. To 'fix' that you can use a Select in Field action on the field trigger to also select the 'Limit' values. Only they need to have a value since nulls cannot be selected. So I also changed the script a litte. You could also choose to do this in your excel sheet. See attached qvw. The triggers can be found on the Triggers sheet of the document properties window.

[andy]

Hi,

I think that will be a little hard to maintain since in the real application I have about 20 dimensions the user may make selections in.

I came up with an other solution that I will try in the real application. I first grab the rows holding the measured data and then concatenate the very same rows but with the filename exchanged to 'Limit' and their value changed to the actual limitvalue via an applyMap. This ensures all dimensions for the reference data having the same dimensions as the real data.

Compared to the first version this does not add any rows just fills the null-values in and the filled in values are just a repeated version so there are no unique values added. The size and performance should be about the same.

/Andy