Adding a reference line connected to a higher value

[#1]

Hi,

I'm struggling to add a reference list that is dyanmic in a way that if you filter, the refence list only shows the higher level.

In our case we can select a value in the available filter tool, but there's another higher level on that value. We managed to set that, but how to set your refence line on that higher level?

In our example, we want to filter for 'TypeBed' and get the results from all nursing departments 'VPE'.

So we have our reference line for this

fx:

(SUM({1<Periode = {'$(vMaxPeriode)'}>}[Score oorzaak]))

/
(SUM({1<[Oorzaak aandoening]={"Altijd","Meestal","Soms","Nooit"}, Periode = {'$(vMaxPeriode)'}>}[Teller]))

But now we would like to add another reference list in which we filter for a value, but get the reference for the higher value to which it is connect.

[reddy-s]

Hi Kim,

Did you give a try with the Max(Aggr(<expression here>)) function as an expression for the new reference line?

[Gysbert_Wassenaar]

{1<Periode = {'$(vMaxPeriode)'}>}

The 1 means 'ignore all selections the user makes'. If you remove that 1 then it will honor the selections. It sounds like that's what you're looking for.

Max(Aggr(

(SUM({<Periode = {'$(vMaxPeriode)'}>}[Score oorzaak]))

/
(SUM({<[Oorzaak aandoening]={"Altijd","Meestal","Soms","Nooit"}, Periode = {'$(vMaxPeriode)'}>}[Teller]))

,YourChartDimensionHere

)

[#4]

Thanks for your suggestion but the calculated field does not display any values...

[#5]

Thanks for your suggestion, have modified load script as:

    "Sessie - OK zaal in tijdstip"  as Inputfield,

    TIME("Sessie - OK zaal in tijdstip",'hh:mm:ss') as Time_in,

    MakeTime(16,15) as Cutoff_time,

    INTERVAL(MakeTime(16,15) - Maketime(10,15),'mm') as Interval_static,

    INTERVAL(MakeTime(16,15) - TIME("Sessie - OK zaal in tijdstip",'hh:mm:ss'),'mm') as Interval_dynamic,

    TIME(INTERVAL(MakeTime(16,15) - TIME("Sessie - OK zaal in tijdstip",'hh:mm'),'mm')) as Interval_dynamic_format,

    interval(maketime(16,15) - frac(date#( "Sessie - OK zaal in tijdstip", 'DD/MM/YYYY hh:mm:ss'))) AS DurationInMinutes

However, the DurationInMinutes column does not show any values.