Hi All,
I've got a hard problem that I'm finding difficult to solve. To really show off the power of Qlik at my company I've had to replicate the insights gathered from an Excel dashboard. So far the project is going well and being received well. However, a sheet that I'm working with has stumped me.
I have 1 dimension called WEEK_ENDING and a measure called FLAG which is either 1 or 0 which I then average.
To calculate the average I use this formula...
Avg({<COLUMN1 = {'Value1', 'Value2'}, COLUMN2 ={'Value3'}, DATE {">=$(=DATE('13/10/2017'))"}>} FLAG)
Ok so this is all well and good and I'm showing this in a line graph no problem. However, within the Excel dashboard, there are 3 other columns showing threshold, target and stretch values which are all the same calculation but use different values. Which I need to show alongside the average to see how we're performing against these three calculated values of threshold, target and stretch.
For simplicity, let's look at just one column of threshold because they are all calculated the same.
The 1st WEEK_ENDING dimension for 13/10/2017 uses the average above.
2nd row needs to be this formula =C2+(C$23-C$3)/COUNT($A$4:$A$23)
C2 is the average for the second WEEK_ENDING dimension, C$23 being the target we wish to hit minus the average from yesterday. Then divide this by the count of week_ending days represented as $A$4:$A$23.
The 3rd row would be the same formula but would use the previous WEEK_ENDING's calculated target
=C3+(C$23-C$3)/COUNT($A$4:$A$23)
So C3 would correspond to the previous row's value.
I understand this is all a bit complicated so I have attached an Excel document which shows my problem. If anyone has an answer it would be greatly appreciated. Or if there is a more intuitive way of calculating threshold, target and stretch in Qlik.
Thanks!
