Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead 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

UrgentNon Urgent
business groupCustomercountryon time+1 daytotalon time+1daytotal
AxA185%89%1254663%70%4534
AxA284%89%785678%81%

5427

AxA378%80%54682%85%43453
AyB189%92%4538575%80%543
AyB292%95%

5468

72%79%

48634

BzC191%96%274371%75%45385
BzC276%84%325479%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
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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

View solution in original post

4 Replies
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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