topic Re: Variables on a specific date using this in other dates in expressions not working in QlikView

Variables on a specific date using this in other dates in expressions not working

qliklizzy — Sat, 16 Nov 2024 01:11:38 GMT

Not sure if this title surfices what i am trying to achieve.

ive been asked to replicate a spreadsheet so it can be live in Qlikview, which can show trajectories on reducing a waitlist and improving performance.

I have set up variables on a snapshot date -Mar19 which show targets, actual, and wl reduction required etc.

i have then tried to use this in a chart to show by month so current shapshot minus Wl reduction required (which is based on Mar-19 snapshot) but it only puts the calulcation in the month of mar19, not every month after - is that becuase im referencing March in the variable?

is there a way of getting around this?

thanks

Re: Variables on a specific date using this in other dates in expressions not working

johnca — Tue, 25 Feb 2020 18:25:16 GMT

Can you share your expression, your variable declaration, maybe an app with some sample data?

Re: Variables on a specific date using this in other dates in expressions not working

qliklizzy — Tue, 25 Feb 2020 18:48:05 GMT

Hi Thanks

i dont tihnk i can it has a lot of patient info in, and a lot of data wouldnt know where or how to chop it down.

pre built app i have taken on.

my variable for pulling total WL size

sum( if(MetricID='RE01' and ScoreCardDate='31/03/2019', Denominator))

my variable for pulling just the breaches

sum( if(MetricID='RE08' and ScoreCardDate='31/03/2019', Denominator))

which gives the performance

($(Traj_Open)-$(Traj_breach))/$(Traj_Open)

i have then calculated a target based on the actual which is 85% predominently (Traj_WLreductionTarget) to then create a Wl reduction figure

IF($(Traj_WLreductionTarget)=0.85,((20*$(Traj_breach))-(3*$(Traj_Open)))/17,IF($(Traj_WLreductionTarget)=0.92,((25*$(Traj_breach))-(2*$(Traj_Open)))/23))

then in a chart i want to create a table that shows for each specialty the number of breaches each month actually, against the target each month which is

=[Breach]-$(Traj_WLReduction)/12

but becuase i have based all my variables on 1 snapshot (which i have to) , the target each month only works for that snapshot - it wont plot by month, i just need to take the wl reduction figure from the number of breaches each month

hope i have made sense. thanks for looking

i think i could easily quit on this, certain things you can do in excel better maybe.

Re: Variables on a specific date using this in other dates in expressions not working

johnca — Wed, 26 Feb 2020 17:14:15 GMT

I used to think Excel was better for certain things.

Please provide a table of sample data so we may serve you better. Change names, etc, if necessary, but I'm not getting a full picture from your descriptions.

Sorry,

John

Re: Variables on a specific date using this in other dates in expressions not working

qliksus — Thu, 27 Feb 2020 03:37:26 GMT

Not sure if i understand you correctly , you are hardcoding for one month in your variable and want to calculate for each month . Removing the month filter and putting the calculation under a month dimension will make it calculate for each month . Its better you attach some sample file with test data explaining your scenario

Re: Variables on a specific date using this in other dates in expressions not working

qliklizzy — Thu, 27 Feb 2020 09:12:36 GMT

ahh thanks - I see I have hard coded a actual snapshot date 31/03/2019 so if I try a month dimension it might work.

thanks! I will try this however I have been taken off this for a time for something more pressing 😞 never get time to develop.

Re: Variables on a specific date using this in other dates in expressions not working

qliklizzy — Thu, 27 Feb 2020 09:14:50 GMT

thanks, don't be sorry - appreciate you looking and what I am doing it quite complex.

I need to work on something else now, so I will get back to this as soon as I can ( typical no development time when I get something juicy)

I will try reduce the app and scramble data and explain better what I am doing

thanks