Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have 2 tables.
One table provides city-wise Targets as below:
Country | State | City | Target |
India | Telangana | Hyderabad | 5 |
India | Telangana | Secunderabad | 2 |
India | Maharashtra | Bombay | 2 |
Canada | Ontario | Toronto | 400 |
Canada | Ontario | Missisauga | 200 |
S.No | City | Status | Date |
1 | Hyderabad | Complete | 20-Feb-19 |
2 | Hyderabad | Complete | 21-Feb-19 |
3 | Hyderabad | Partial | 21-Feb-19 |
4 | Bombay | Complete | 22-Feb-19 |
5 | Bombay | Complete | 22-Feb-19 |
I want to show a KPI which says # of centers whose completion percentage is < 50%
so here it should say 4 because only bombay is complete 100%.
if Status completed you want to show as 4
because for bomaby target is 2..
=sum({<City={'Bombay'},State={'Maharashtra'},Status={'Complete'}>} Target)
=count({<City={'Bombay'},State={'Maharashtra'},Status={'Complete'}>} Status)
Thanks but this will not help as I cannot hardcode city. Plus I am looking for count of citys where target is < 50% achieved
So basically I am looking at combining two steps
1. Calculate percentage completion for each city. This will be Count(Status=Complete)/Target for each city
2. Count of all the entries below 50% in step 1 above
see the attach
it has expression and QVF
Hi Channa,
Thanks for sharing the file. This is displaying a table. I am looking for a KPI. I have attached my attempt to articulate.
=count({<Status={'Complete'},City={'Bombay'}>} Status)/sum({<Status={'Complete'},City={'Bombay'}>} Target)
it is for bombay