Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Highlighted
acbishop2
Contributor

Average Regardless of any Filters / Dimensions

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:

AppIdABA-to-B
123453/11/20193/29/201918
123463/12/20193/24/201912
123473/13/20193/26/201913
123483/13/20194/1/201919
123493/13/20194/1/201919
123503/19/2019  
123513/19/2019  
123523/20/2019  
123533/20/2019  
123543/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:

AppIdABA-to-BAtoBAvgProjectedB
123453/11/20193/29/20191816.2 
123463/12/20193/24/20191216.2 
123473/13/20193/26/20191316.2 
123483/13/20194/1/20191916.2 
123493/13/20194/1/20191916.2 
123503/19/2019  16.24/4/2019
123513/19/2019  16.24/4/2019
123523/20/2019  16.24/5/2019
123533/20/2019  16.24/5/2019
123543/21/2019  16.24/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:

AppIdABA-to-BAtoBAvgProjectedB
123453/11/20193/29/20191818 
123463/12/20193/24/20191212 
123473/13/20193/26/20191313 
123483/13/20194/1/20191919 
123493/13/20194/1/20191919 
123503/19/2019    
123513/19/2019    
123523/20/2019    
123533/20/2019    
123543/21/2019    

 

Hopefully I'm making sense. Please let me know if any clarification is needed. 

Thanks in advance for the help.

1 Solution

Accepted Solutions

Re: Average Regardless of any Filters / Dimensions

May be try this

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

Re: Average Regardless of any Filters / Dimensions

May be try this

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

Re: Average Regardless of any Filters / Dimensions

{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
Contributor

Re: Average Regardless of any Filters / Dimensions

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

Partner
Partner

Re: Average Regardless of any Filters / Dimensions

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

acbishop2
Contributor

Re: Average Regardless of any Filters / Dimensions

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

Partner
Partner

Re: Average Regardless of any Filters / Dimensions

no problem.
glad to help