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%.

[SadlerS]

Percentage can be calculated by dividing the value by the total value, and then multiplying the result by 100. The formula used to calculate percentage is: (value/total value)×100%.

TellHappyStar.com Survey

[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