Average of a complex Variable

[leocattqv]

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

[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]

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. 

MonthYear	Complaints	Ship	Total Complaints	Total Ship	PBar	Zi	MR	SigmaZ
	27961	7801502	27961	7801502	0.003584053	0	8.335874997	-
01-2017	1576	428151	27961	7801502	0.003584053	1.060898765	-	-
02-2017	1359	415543	27961	7801502	0.003584053	-3.383165321	4.444064086	3.939773126
03-2017	1699	475255	27961	7801502	0.003584053	-0.105329502	3.277835819	2.905882818
04-2017	1450	460970	27961	7801502	0.003584053	-4.982080512	4.87675101	4.323360824
05-2017	1594	549739	27961	7801502	0.003584053	-8.492614045	3.510533533	3.112175118
06-2017	1631	523117	27961	7801502	0.003584053	-5.642452496	2.850161549	2.526738962
07-2017	1710	480893	27961	7801502	0.003584053	-0.326878138	5.315574358	4.712388615
08-2017	1691	460742	27961	7801502	0.003584053	0.978119701	1.304997838	1.156912977
09-2017	1633	533485	27961	7801502	0.003584053	-6.39286936	7.37098906	6.53456477
10-2017	3650	567947	27961	7801502	0.003584053	35.84779755	42.24066691	37.44739974
11-2017	3063	544482	27961	7801502	0.003584053	25.20743519	10.64036236	9.432945354
12-2017	1495	473765	27961	7801502	0.003584053	-4.935202712	30.14263791	26.72219672
01-2018	1465	443470	27961	7801502	0.003584053	-3.126447287	1.808755425	1.603506582
02-2018	1252	455190	27961	7801502	0.003584053	-9.410708809	6.284261522	5.571153831
03-2018	1407	510199	27961	7801502	0.003584053	-9.876501085	0.465792276	0.412936415
04-2018	1286	478554	27961	7801502	0.003584053	-10.38124238	0.504741297	0.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]

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?

[sunny_talwar · Accepted solution]

If this is what you want,

try this

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

[leocattqv]

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]

That worked,  thank you so much