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
