topic Average of a complex Variable in QlikView

Average of a complex Variable

leocattqv — Tue, 15 May 2018 05:34:11 GMT

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

Re: Average of a complex Variable

sunny_talwar — Tue, 15 May 2018 11:24:28 GMT

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?

Re: Average of a complex Variable

leocattqv — Tue, 15 May 2018 13:51:16 GMT

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

Re: Average of a complex Variable

sunny_talwar — Tue, 15 May 2018 13:54:33 GMT

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.

Re: Average of a complex Variable

leocattqv — Tue, 15 May 2018 14:33:31 GMT

Here you go

Re: Average of a complex Variable

sunny_talwar — Tue, 15 May 2018 14:53:57 GMT

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

Re: Average of a complex Variable

sunny_talwar — Tue, 15 May 2018 14:56:19 GMT

If this is what you want,

try this

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

Re: Average of a complex Variable

leocattqv — Tue, 15 May 2018 15:42:00 GMT

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

Re: Average of a complex Variable

sunny_talwar — Tue, 15 May 2018 15:44:24 GMT

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

Re: Average of a complex Variable

leocattqv — Tue, 15 May 2018 15:49:27 GMT

That worked, thank you so much

Re: Average of a complex Variable

leocattqv — Tue, 15 May 2018 16:23:33 GMT

I just mean that as I slice the data - the average should change with the slices,

Re: Average of a complex Variable

sunny_talwar — Tue, 15 May 2018 16:25:22 GMT

So, this did work, right?

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

Re: Average of a complex Variable

Shras1609 — Tue, 12 Dec 2023 17:08:12 GMT

I'm trying to do Laney P Chart chart in the Qlik Sense. Can you share the .qvd file with Laney P Chart for Qlik sense? Thanks!