New to QlikView, need help in scripting/formula for aggregation

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hello All,

I am new to this Qlik world, learnt it few months back and here I am with a condition which I am facing challenges, and need some help and guidance to solve.

I have a data set which includes 2 dimensions (Region & City), and 5 calculated columns/KPIs ('A', 'B', 'C', 'D', and 'E') based on measures. First 4 cols are straightforward and I have calculated them using formulas, and they are resulting in a certain % value. I need help in the 5th column. It should be based on column ('B') and show aggregate % per City or Region, based on selection.

First help needed for below calculation -

Formula for column 'E' (with ?) should be =>

When any city is selected,

=> ((count of servers in City which has >= 80% in Col 'B'/total servers in that City)*100 = resulting in a % value in that column if any city is selected from City filter/listbox.

When any Region is selected,

=> ((count of servers in Region which has >=80% in Col 'B')/(total servers in that Region))*100 = resulting in a % value in that column if any Region is selected from Region filter/listbox.

Second help need for the below calculation -

RAG Status per Region, and RAG Status per KPI.

Reginional RAG - Static overall Regional RAG based on highest RAG in all KPIs (in cols A, B, C,D, E) in that Region. If any KPI has red in it for that region, it should appear in red.

KPI RAG - RAG for that particular KPI, based on selection of City or Region.

APA - RAG		AMR - RAG	EME - RAG

A-RAG	B-RAG	C-RAG	D-RAG	E-RAG

Region	City	Server	A	B	C	D	E
APA	Delhi	ServerDA1	58	68	41	67	?
APA	Delhi	ServerDA2	47	44	63	74	?
APA	Mumbai	ServerMA1	79	73	55	61	?
APA	Mumbai	ServerMA2	90	44	72	88	?
AMR	New York	ServerNYA1	64	77	47	75	?
AMR	New York	ServerNYA2	88	47	84	43	?
AMR	Chicago	ServerCHA1	51	79	56	60	?
AMR	Chicago	ServerCHA2	61	71	48	43	?
EME	Paris	ServerPE1	61	46	63	61	?
EME	Paris	ServerPE2	41	89	66	55	?
EME	London	ServerLE1	81	90	57	75	?
EME	London	ServerLE2	54	50	47	88	?

I am attaching the excel sheet with the sample data and expected solutions.

I am looking forward to Qlik Community for guidance and it has been a great learning so far. Thank you so much.

Regards,

Avi

topic New to QlikView, need help in scripting/formula for aggregation in QlikView

New to QlikView, need help in scripting/formula for aggregation