Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
Good evening.
I need your help in Qlikview application.
Please find the attached files containg My application and spread sheet(data).
I have the following inline table-
Start Range | End Range | Performance Value |
---|---|---|
99% | 100% | Outstanding |
98% | 99% | Achived |
97.01% | 97.90% | Average |
0 | 97% | Not Achived |
I want to output like the below format based on Inline table and attached spreadsheet-
Service name | Achived % | Performance Status |
A | Calculation=Closed Ticket for service A*100/Total Ticket for service A *As per this example-100% | Outstanding(Based on inline table Range) |
B | Calculation=Closed Ticket for service B*100/Total Ticket for service B *As per this example-100% | Outstanding(Based on inline table Range) |
C | Calculation=Closed Ticket for service C*100/Total Ticket for service C *As per this example-50% | Not achived(Based on inline table Range) |
You can achieve it from front end itself instead of going through the complex sceripting
Create the Straight Table
Dimension : Service Name
Expressions:
1) % Achieved
num(Count(DISTINCT{<Status={'Closed'}>}[Ticket No]) /Count(TOTAL <[Service name]> DISTINCT [Ticket No]),'#.#00%')
2) Performance Value
=if([% Achieved]>=0.99 and [% Achieved]<=1,'Outstanding',
if([% Achieved]>=0.98 and [% Achieved] <=0.99,'Achieved',
if([% Achieved]>=0.9701 and [% Achieved] <=0.9790,'Average',
if([% Achieved]>=0 and [% Achieved] <=0.97,'Not Achieved'))))
3) Performance Status
=if([% Achieved]>=0.98 and [% Achieved]<=1,'Outstanding',
if([% Achieved]>=0 and [% Achieved] <=0.9790,'Not Achieved'))
See the attached
Please help
i think you can use intervalmatch in the data load script to match the score with the correct interval (range).
There are a lot of examples in the community using date ranges and interval matching, but its function that fundamentally understands number ranges (and dates just happen to be able to be represented numerically).
Does your data report the '%' values on a row by row basis ?
Hi Sesa,
You can IntervalMatch function in this case. I just did everything in load script. Check the attachment and let me know.
You can achieve it from front end itself instead of going through the complex sceripting
Create the Straight Table
Dimension : Service Name
Expressions:
1) % Achieved
num(Count(DISTINCT{<Status={'Closed'}>}[Ticket No]) /Count(TOTAL <[Service name]> DISTINCT [Ticket No]),'#.#00%')
2) Performance Value
=if([% Achieved]>=0.99 and [% Achieved]<=1,'Outstanding',
if([% Achieved]>=0.98 and [% Achieved] <=0.99,'Achieved',
if([% Achieved]>=0.9701 and [% Achieved] <=0.9790,'Average',
if([% Achieved]>=0 and [% Achieved] <=0.97,'Not Achieved'))))
3) Performance Status
=if([% Achieved]>=0.98 and [% Achieved]<=1,'Outstanding',
if([% Achieved]>=0 and [% Achieved] <=0.9790,'Not Achieved'))
See the attached
Note that, you need to change start range and end range in front end whenever the KPI revised.