Need help in solving a complex condition

[Anonymous]

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 >= 75% 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 >=75% 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.

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