Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
leocattqv
Creator
Creator

Average of a complex Variable

Hi Community,

I am creating a Laney P Chart and am at the final stages.

This is a pretty complex calculation that puts variables upon variables.  The last stage is to average the Z-Score over 12 months

I have attached an App.  the Variable I am trying to get to is vMRBar (Aggr(avg(($(vZi)-Above($(vZi))))))

I Havent worked with Aggr alot, Im not sure its the way to get what I need.

Any suggestions would be appreciated.

thanks

-L

1 Solution

Accepted Solutions
sunny_talwar

If this is what you want,

Capture.PNG

try this

avg(TOTAL Aggr($(vMR),MonthYear))

View solution in original post

12 Replies
sunny_talwar

What is the output you are getting right now? and what do you expect to get?

Also, it seems that I am unable to get output from vZi... is that working for you? Is the problem in vMRBar or is it starting with another variable?

leocattqv
Creator
Creator
Author

Hi Sunny,

I am getting output from Zi that is working fine.  what I need is the average of Zi over the months.  I am able to get that in the total row, but not in the data itself. (Yellow Column)

The issue I think is that I am trying to average a variable that has a sum (multiple actually) in it. 

MonthYearComplaintsShipTotal ComplaintsTotal ShipPBarZiMRSigmaZ
2796178015022796178015020.00358405308.335874997-
01-201715764281512796178015020.0035840531.060898765--
02-201713594155432796178015020.003584053-3.3831653214.4440640863.939773126
03-201716994752552796178015020.003584053-0.1053295023.2778358192.905882818
04-201714504609702796178015020.003584053-4.9820805124.876751014.323360824
05-201715945497392796178015020.003584053-8.4926140453.5105335333.112175118
06-201716315231172796178015020.003584053-5.6424524962.8501615492.526738962
07-201717104808932796178015020.003584053-0.3268781385.3155743584.712388615
08-201716914607422796178015020.0035840530.9781197011.3049978381.156912977
09-201716335334852796178015020.003584053-6.392869367.370989066.53456477
10-201736505679472796178015020.00358405335.8477975542.2406669137.44739974
11-201730635444822796178015020.00358405325.2074351910.640362369.432945354
12-201714954737652796178015020.003584053-4.93520271230.1426379126.72219672
01-201814654434702796178015020.003584053-3.1264472871.8087554251.603506582
02-201812524551902796178015020.003584053-9.4107088096.2842615225.571153831
03-201814075101992796178015020.003584053-9.8765010850.4657922760.412936415
04-201812864785542796178015020.003584053-10.381242380.5047412970.447465689
sunny_talwar

Would you be able to repost the app where vZi is working? I don't think it is working in the attached app or I don't know where to use it in order to make it work.

leocattqv
Creator
Creator
Author

Here you go

sunny_talwar

Are you looking to get the same value on each row? So, in this case 5.34 should repeat for all Months?

Capture.PNG

sunny_talwar

If this is what you want,

Capture.PNG

try this

avg(TOTAL Aggr($(vMR),MonthYear))

leocattqv
Creator
Creator
Author

that is correct

What I am looking for is a dynamic way to get the average - it needs to be a constant in my formula

sunny_talwar

Dynamic? What exactly do you mean? I am not 100% sure I understand...

leocattqv
Creator
Creator
Author

That worked,  thank you so much