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.
