Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
thank you for your guidance Oleg, I did indeed two set analysis expression and a simple count(OrderLines).
Sometimes it is really so easy