topic pivot with a mix of percentages and absolute values in QlikView

pivot with a mix of percentages and absolute values

Mon, 03 Mar 2014 10:18:15 GMT

dear all,

I need to create an hybrid absolute/percentage values pivot chart, which I think is pretty challenging and have no idea how to solve it.

my source datatable is organised per lines shipped, with some information to measure shipment performance (order date, shipped date etc) and some general info (typology of customer, urgency of order, country etc.)

the expected outcome looks somewhat like below

			Urgent			Non Urgent
business group	Customer	country	on time	+1 day	total	on time	+1day	total
A	x	A1	85%	89%	12546	63%	70%	4534
A	x	A2	84%	89%	7856	78%	81%	5427
A	x	A3	78%	80%	546	82%	85%	43453
A	y	B1	89%	92%	45385	75%	80%	543
A	y	B2	92%	95%	5468	72%	79%	48634
B	z	C1	91%	96%	2743	71%	75%	45385
B	z	C2	76%	84%	3254	79%	83%	2424

basically, the total is showing the absolute number of order lines per lowest detail (e.g., 12546 lines of urgent orders shipped to country A1, for customer x, for product type A)

the <on time> and <+1day> show respectively the percentage of lines shipped on time and cumulative percentage of lines shipped with 1 day delay (e.g. 85% of the 12546 lines were shipped on time and 89% of the 12546 lines were shipped with a delay of 1 day)

how easily implementable is this in QV?

thank you

Re: pivot with a mix of percentages and absolute values

Oleg_Troyansky — Mon, 03 Mar 2014 20:53:26 GMT

Looks like a fairly easy task:

You will have 4 dimensions (Busines group, Customer, Country and Urgent/Not Urgent) and 3 Expressions - On time, +1Day, and Total. Each expression will have its own formula to calculate the result and its own number formatting.

Am I missing any hidden problems?

cheers,

Oleg Troyansky

Re: pivot with a mix of percentages and absolute values

Tue, 04 Mar 2014 08:52:38 GMT

Dear Oleg,

I missed to explain it correctly.

'On time', '+1day' (and 'delayed' , not to be visualised) are the three values to be found in another dimension, Delay.

The expression is simply count of orderlines

the first 85%:

is the count of orderlines which are urgent order, business group A, customer x, country A1 which are shipped on time, divided by the total count of orderlines which are urgent order, business group A, customer x, country A1

then the 89%:

is the count of orderlines which are urgent order, business group A, customer x, country A1 which are shipped on time cumulated to the lines which are shipped +1day, divided by the total count of orderlines which are urgent order, business group A, customer x, country A1

the 12546 is the total count of orderlines which are urgent order, business group A, customer x, country A1

I hope it is clearer!

thank you

Re: pivot with a mix of percentages and absolute values

Oleg_Troyansky — Tue, 04 Mar 2014 18:20:49 GMT

Well, in this case, you have two choices:

1. Create 3 individual expressions and use Set Analysis to filter the relevant value of "on Time", ...

2. Or, Add that field as a dimension and use a conditional formula, something like this:

IF (Status = 'On Time', num( ...., '#,##0.0%') ,

IF (Status = 'Total' , num( ..... '#,##0')

))

In this case, set the Number formatting to "Expression Default"

I haven't tested, so there might be some underwater stones there, but generally it should work.

cheers,

Oleg

Re: pivot with a mix of percentages and absolute values

Tue, 11 Mar 2014 11:00:47 GMT

thank you for your guidance Oleg, I did indeed two set analysis expression and a simple count(OrderLines).

Sometimes it is really so easy