New to QlikView, need help in scripting/formula for aggregation
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.
