Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello All,
I've hit an obstacle that I'm hoping will be easily resolved. I have a bunch of applications ("Apps") each with a value that represents how long it took that app to get from point A to point B (speed in days). I need to be able to project how long it will take other apps that haven't hit point B yet based off of the total average speed for apps that have already made it to point B.
My table essentially looks like this:
AppId | A | B | A-to-B |
12345 | 3/11/2019 | 3/29/2019 | 18 |
12346 | 3/12/2019 | 3/24/2019 | 12 |
12347 | 3/13/2019 | 3/26/2019 | 13 |
12348 | 3/13/2019 | 4/1/2019 | 19 |
12349 | 3/13/2019 | 4/1/2019 | 19 |
12350 | 3/19/2019 | ||
12351 | 3/19/2019 | ||
12352 | 3/20/2019 | ||
12353 | 3/20/2019 | ||
12354 | 3/21/2019 |
As stated earlier, based on these "A-to-B" values, I need to make projections for apps that have hit point A but not point B. My thought is that the easiest way to do this would be to create a variable, vAtoBAvg, that would take the average of all A-to-B values, and then add that to the A-values to get a projected B value. The desired result would be something like the following:
AppId | A | B | A-to-B | AtoBAvg | ProjectedB |
12345 | 3/11/2019 | 3/29/2019 | 18 | 16.2 | |
12346 | 3/12/2019 | 3/24/2019 | 12 | 16.2 | |
12347 | 3/13/2019 | 3/26/2019 | 13 | 16.2 | |
12348 | 3/13/2019 | 4/1/2019 | 19 | 16.2 | |
12349 | 3/13/2019 | 4/1/2019 | 19 | 16.2 | |
12350 | 3/19/2019 | 16.2 | 4/4/2019 | ||
12351 | 3/19/2019 | 16.2 | 4/4/2019 | ||
12352 | 3/20/2019 | 16.2 | 4/5/2019 | ||
12353 | 3/20/2019 | 16.2 | 4/5/2019 | ||
12354 | 3/21/2019 | 16.2 | 4/6/2019 |
I run into problems with the variable, which doesn't seem to be independent of dimensions. My variable AtoBAvg has the formula:
Avg({1}[A-to-B])
And here's what I'm getting:
AppId | A | B | A-to-B | AtoBAvg | ProjectedB |
12345 | 3/11/2019 | 3/29/2019 | 18 | 18 | |
12346 | 3/12/2019 | 3/24/2019 | 12 | 12 | |
12347 | 3/13/2019 | 3/26/2019 | 13 | 13 | |
12348 | 3/13/2019 | 4/1/2019 | 19 | 19 | |
12349 | 3/13/2019 | 4/1/2019 | 19 | 19 | |
12350 | 3/19/2019 | ||||
12351 | 3/19/2019 | ||||
12352 | 3/20/2019 | ||||
12353 | 3/20/2019 | ||||
12354 | 3/21/2019 |
Hopefully I'm making sense. Please let me know if any clarification is needed.
Thanks in advance for the help.
May be try this
Avg({1}TOTAL [A-to-B])
{1} ignores selection of filters not dimensions on your chart.
you need total qualifier
e.g. ignore all the dimensions in your table and calculate global average
Avg(Total [A-to-B])
for more info
https://community.qlik.com/t5/Qlik-Design-Blog/What-does-the-TOTAL-qualifier-do/ba-p/1472990
Thank you for the fast response!
It looks like that will do that trick, but one quick question before I close this up:
I tested this with a non-script variable, but the same formula doesn't seem to be working in the load script. Is there a way to do that?
What I have:
Let vAtoBAvg = Avg({1}TOTAL [A-to-B]);
(I'm asking because I want the final product (ProjectedB) to be recognized as a Date field so I can use the autoCalendar with it.)
total qualifier does not work in script mode.
You can add the variable in qlik app directly though
Ok, then I'll just have to stick to the app variable. Thank you both for the help.