Getting a Total Percentage

[lsmith48461]

I have a chart where I want to show quantity delivered  on the due date is or isn't the same as the actual ship date. The Dimension is City. I have two expressions:

Order Qty                                      =sum(OrderQuantity)

and

Order Qty On Time                   =If(DueDate = ShipDate, sum(OrderQuantity), 0)

Everything is fine except when I add a third column for percentage.

% Order Qty On Time              =[Order Qty On Time] / [Order Qty]

The column detail naturally shows either 100% or 0%

The problem is on the Total line where nothing I have tried shows the correct percentage which should be around 85%. I've tried multiple combinations using the AGGR or TOTAL functions, but nothing seems to work.

Suggestions?

See below. 

It's either 

[abhijitnalekar]

Hi @lsmith48461 ,

Have you tried the below-highlighted property of measure?

[lsmith48461]

Where is that? I'm in Qlikview, not Qlik Sense.

[abhijitnalekar]

Hi @lsmith48461 ,

You can get the same in QlikView below Expressions Tab

[lsmith48461]

Tried that. Unfortunately doesn't work. Here's what I'm getting in the chart. The totals for Order Qty and Order Qty On Time are correct, but the total percentage should be around 85%.

[abhijitnalekar]

Hi @lsmith48461 ,

Please keep that option selected to Expression total

if that doesn't work, is it possible to share the sample data? 

[Blalock69]

---

@lsmith48461 wrote: TargetPayandBenefits

I have a chart where I want to show quantity delivered  on the due date is or isn't the same as the actual ship date. The Dimension is City. I have two expressions:

Order Qty                                      =sum(OrderQuantity)

and

Order Qty On Time                   =If(DueDate = ShipDate, sum(OrderQuantity), 0)

Everything is fine except when I add a third column for percentage.

% Order Qty On Time              =[Order Qty On Time] / [Order Qty]

The column detail naturally shows either 100% or 0%

The problem is on the Total line where nothing I have tried shows the correct percentage which should be around 85%. I've tried multiple combinations using the AGGR or TOTAL functions, but nothing seems to work.

Suggestions?

See below. 

It's either 

---

I'm getting in the chart. The totals for Order Qty and Order Qty On Time are correct, but the total percentage should be around 85%.

[Prasad_Kanse]

I tried this calculation on Sample Superstore Data.

Order Qty                                      =sum(OrderQuantity)

and

Order Qty On Time                   =sum(if([Order Date]=[Ship Date],([Order Quantity]),0))

Everything is fine except when I add a third column for percentage.

% Order Qty On Time              =sum(if([Order Date]=[Ship Date],([Order Quantity]),0))

                                                             / sum(OrderQuantity)

[Anonymous]

Can you modify the script?

Maybe this?

I added a City field in the initial load (as you alluded to), then a preceding load for the [Order Qty On Time] value with the same expression...

data:
Load *,
if([DueDate]=[ShipDate],[OrderQuantity],0) as [OrderQtyOnTime];

Load * Inline [City|ShipDate|DueDate|OrderQuantity
Superstore|01-11-2022||64
Superstore|01-12-2022||35
Superstore|01-14-2022||490
Superstore|01-17-2022|01-17-2022|53
Superstore|01-17-2022|02-03-2022|120
Superstore|01-17-2022||658
Superstore|01-21-2022||6000
Superstore|01-28-2022||1
Superstore|02-09-2022|02-09-2022|6
Superstore|02-11-2022||50
Superstore|02-14-2022|02-14-2022|220
Superstore|02-14-2022||100
Superstore|02-18-2022|02-18-2022|159
Superstore|02-25-2022|02-25-2022|1
Superstore|03-04-2022|03-04-2022|432
Superstore|03-04-2022||5000
Superstore|03-07-2022||100
Superstore|03-11-2022||50
Other Store|01-11-2022||33
Other Store|01-12-2022||27
Other Store|01-14-2022||439
Other Store|01-17-2022|01-17-2022|62
Other Store|01-17-2022|02-03-2022|12
Other Store|01-17-2022||121
Other Store|01-21-2022||456
Other Store|01-28-2022||10
Other Store|02-09-2022|02-09-2022|6
Other Store|02-11-2022||55
Other Store|02-14-2022|02-14-2022|112
Other Store|02-14-2022||111
Other Store|02-18-2022|02-18-2022|19
Other Store|02-25-2022|02-25-2022|19
Other Store|03-04-2022|03-04-2022|42
Other Store|03-04-2022||500
Other Store|03-07-2022||1001
Other Store|03-11-2022||502
]
(ansi, txt, delimiter is '|', embedded labels);

Then in the chart the dimensions are City, ShipDate & DueDate

And the expressions are

Order Qty = Sum(OrderQuantity)

Order Qty On Time = Sum(OrderQtyOnTime)

% Order Qty On Time = Sum(OrderQtyOnTime)/Sum(Total <City,DueDate,ShipDate> OrderQuantity)

HTH,

John

[njmaehler]

The problem is that Qlik evaluates each expression / measure at an individual line level. You do NOT have either date at the total line level therefore  you might want a different expression for the top line. 
Best way to achieve this is to use variables and make sure that you use the equals sign in front to ensure the variable disregards the dimension. 
Just throwing a curve ball here because you said it is either 0% or 100% - does that mean you ALWAYS deliver in full and never do part deliveries? Base on your answer, you could either do a count of your dates if always full deliveries or you will need to sum(OrderQuantity) if you do allow partial deliveries. 

Variables:
vOrderQtyTotal   =sum(OrderQuantity)
vOrderQtyOnTimeTotal   =sum(if([Order Date]=[Ship Date],([Order Quantity]),0))

if(Dimensionality() = 0, //this is the top line where Dimensionality() = 0 - might need to use a pivot table here
              $(vOrderQtyOnTimeTotal) / $(vOrderQtyTotal)
, //this is the else i.e. not the top TOTAL line
              sum(if([Order Date]=[Ship Date],([Order Quantity]),0))   / sum(OrderQuantity)
)