Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
qliklizzy
Creator II
Creator II

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

Hi

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

 

Labels (2)
6 Replies
johnca
Specialist
Specialist

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

qliklizzy
Creator II
Creator II
Author

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.

 

 

johnca
Specialist
Specialist

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

qliksus
Specialist II
Specialist II

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   

qliklizzy
Creator II
Creator II
Author

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.

 

 

qliklizzy
Creator II
Creator II
Author

 

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