topic Re: Average Regardless of any Filters / Dimensions in App Development

Average Regardless of any Filters / Dimensions

acbishop2 — Sat, 16 Nov 2024 21:05:36 GMT

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.

Re: Average Regardless of any Filters / Dimensions

sunny_talwar — Mon, 08 Apr 2019 18:44:24 GMT

May be try this

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

Re: Average Regardless of any Filters / Dimensions

dplr-rn — Mon, 08 Apr 2019 18:46:08 GMT

{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

Re: Average Regardless of any Filters / Dimensions

acbishop2 — Mon, 08 Apr 2019 18:54:22 GMT

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

Re: Average Regardless of any Filters / Dimensions

dplr-rn — Mon, 08 Apr 2019 19:06:40 GMT

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

Re: Average Regardless of any Filters / Dimensions

acbishop2 — Mon, 08 Apr 2019 19:07:50 GMT

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

Re: Average Regardless of any Filters / Dimensions

dplr-rn — Mon, 08 Apr 2019 19:08:36 GMT

no problem.
glad to help