Vertical reference line based on graph itself

Anonymous — Thu, 26 Jul 2018 07:40:05 GMT

Hello all,

the example is imagined, the problem very real in another application -

I have a linechart based on days since store opening as X-Axis (dimension) and sales as Y-Axis (expression).

I want to draw a vertical reference line where my sales drop below 500. I need to calculate it in the reference line expression as it can be subject change (different stores being filtered).

Once the sales drop below 500 they never go above 500 again (imagine a line going from the top left to the bottom right).

How do I identify/calculate the "day" on which the sales dropped below 500 in an expression?

If you need additional information, please ask:)

Re: Vertical reference line based on graph itself

Anonymous — Thu, 26 Jul 2018 09:04:27 GMT

I have found a (partial) solution - formula inside the reference line expression:

= FirstSortedValue ( daysSinceOpening,

-Aggr(

IF(SUM(Sales)<500, SUM(Sales), 0),

daysSinceOpening

)

This gives me the first day on which Sales was below 500.

However, this is calculated on a day to day basis. (Which does solve the problem mentioned by me in the question, but as it turns out not my initial problem).

When I want to find out the week (since store opening) on which sales was below 500 I encounter another problem of the function not returning any value anymore. Below you can see my function where I try to aggregate on a weekly setting:

= FirstSortedValue ( Floor(daysSinceOpening, 7),

-Aggr(

IF(SUM(Sales)<500, SUM(Sales), 0),

Floor(daysSinceOpening, 7)

)

topic Vertical reference line based on graph itself in QlikView

Vertical reference line based on graph itself

Re: Vertical reference line based on graph itself