Average Regardless of any Filters / Dimensions

[acbishop2]

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.

[sunny_talwar · Accepted solution]

May be try this

Avg({1}TOTAL [A-to-B])

[dplr-rn]

{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

[acbishop2]

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.)

[dplr-rn]

total qualifier does not work in script mode.
You can add the variable in qlik app directly though

[acbishop2]

Ok, then I'll just have to stick to the app variable. Thank you both for the help.

[dplr-rn]

no problem.
glad to help