Announcements
cancel
Showing results for
Did you mean:
Not applicable

## pivot with a mix of percentages and absolute values

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

 business group Customer country +1 day total on time +1day total Urgent Non Urgent on time 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

1 Solution

Accepted Solutions

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

4 Replies

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

Not applicable
Author

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

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

Not applicable
Author

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

Sometimes it is really so easy

Community Browser