topic Re: Getting a Total Percentage in QlikView

Getting a Total Percentage

lsmith48461 — Thu, 31 Mar 2022 17:42:09 GMT

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

Re: Getting a Total Percentage

abhijitnalekar — Thu, 31 Mar 2022 19:51:29 GMT

Hi @lsmith48461 ,

Have you tried the below-highlighted property of measure?

Re: Getting a Total Percentage

lsmith48461 — Fri, 01 Apr 2022 13:50:10 GMT

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

Re: Getting a Total Percentage

abhijitnalekar — Fri, 01 Apr 2022 14:02:41 GMT

Hi @lsmith48461 ,

You can get the same in QlikView below Expressions Tab

Re: Getting a Total Percentage

lsmith48461 — Fri, 01 Apr 2022 14:10:01 GMT

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

Re: Getting a Total Percentage

abhijitnalekar — Fri, 01 Apr 2022 14:23:57 GMT

Hi @lsmith48461 ,

Please keep that option selected to Expression total

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

Re: Getting a Total Percentage

Blalock69 — Wed, 06 Apr 2022 04:17:30 GMT

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

Re: Getting a Total Percentage

Prasad_Kanse — Tue, 12 Apr 2022 16:20:51 GMT

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)

Re: Getting a Total Percentage

Anonymous — Tue, 12 Apr 2022 21:52:35 GMT

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

Re: Getting a Total Percentage

njmaehler — Wed, 13 Apr 2022 09:42:27 GMT

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)
)

Re: Getting a Total Percentage

vinieme12 — Tue, 19 Apr 2022 10:49:34 GMT

Try below

OrderQtyOnTime %

sum( AGGR( If(DueDate = ShipDate, sum(OrderQuantity), 0) , YourChartDimension1,YourChartDimension2) )

sum(OrderQuantity)