Look up a value, which is based on a set expression, in chart

[poortman]

Hello Qlik-users,

I need your help.

In a chart (see printscreen below) I've calculated the median per weekday (In Dutch: WeekDag) and weeknumber, of no. of admissions (In Dutch: Aantal opnames), for the year 2020 with the following expression:

=median(aggr(count({<Opnamejaar = {'2020'}>}Distinct Opnamenummer),OpnameWeekdag, OpnameWeeknr))

Now I want to use this calculated median per weekday as 'predicted no. of admissions' (In Dutch: Verwacht aantal opnames) in an other chart with dates in the future. Usually I would use the look-up-function in Excel.. but I can't find a way to do this in a chart in Qlik Sense. The chart will look like this, where the last column is the look-up value, based on the chart in the first printscreen.:

I can't do this in the script, because I want to give the user the flexibility to filter on different kind of dimensions. So the median of 2020 will change with selections from the user.

Is there a way I can look up the value in this second chart, based on field 'WeekDag' (=WeekDay)? 

Kind regards,

Niek Poortman

[marcus_sommer]

It's not possible to refer / lookup for calculations within another UI objects. If you need those values within another chart you will need to perform the calculations there, too.

- Marcus

[poortman · Accepted solution]

Hi Marcus,

Thanks for your reply. I've managed to solve it with NPrinting.

1. The median of last year's admissions will be calculated with an set expressions, based on applied filters by user(s).

2. NPrinting exports this data into Excel. In Excel I've built a template where the median from last year will be applied to datefields from this year (so when the median of 2020 for wednesdays is 8, wednesday 10 february 2021 will get the value 8). 

3. Qlik Sense imports the Excel and uses the median to predict future admissions per weekday for the coming 14 days. 

A bit of a work-around, but it works fine for now! 

Kind regards,

Niek Poortman