Skip to main content
Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. IMPORTANT DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Vertical reference line based on graph itself

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:)

1 Reply
Anonymous
Not applicable
Author

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)

    )

)