topic Re: Help in Qlikview chart calculation in QlikView

Help in Qlikview chart calculation

Tue, 01 Dec 2015 15:38:02 GMT

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 A100/Total Ticket for service A As per this example-100%	Outstanding(Based on inline table Range)
B	Calculation=Closed Ticket for service B100/Total Ticket for service B As per this example-100%	Outstanding(Based on inline table Range)
C	Calculation=Closed Ticket for service C100/Total Ticket for service C As per this example-50%	Not achived(Based on inline table Range)

Re: Help in Qlikview chart calculation

Wed, 02 Dec 2015 00:44:27 GMT

Please help

Re: Help in Qlikview chart calculation

JonnyPoole — Wed, 02 Dec 2015 03:18:01 GMT

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 ?

Re: Help in Qlikview chart calculation

tamilarasu — Wed, 02 Dec 2015 04:21:31 GMT

Hi Sesa,

You can IntervalMatch function in this case. I just did everything in load script. Check the attachment and let me know.

Re: Help in Qlikview chart calculation

Kushal_Chawda — Wed, 02 Dec 2015 06:01:22 GMT

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

Re: Help in Qlikview chart calculation

tamilarasu — Wed, 02 Dec 2015 07:08:48 GMT

Note that, you need to change start range and end range in front end whenever the KPI revised.