topic Re: Conditional Sum on Conditional Dimension in QlikView

Conditional Sum on Conditional Dimension

skompel2 — Sat, 16 Nov 2024 04:40:43 GMT

I have this table:

Code	Part Desc	Location	Lot Size	Complete Qty
Bar	101 (TD only)	1	1	0
Bar	102	X	1	5
Bar	101 (TD ONLY)	2	1	0
Stick	A10	X	1	1
Stick	A11 (TD ONLY)	2	1	0
Stick	A11 (TD ONLY)	1	1	1
Bar	102	Y	1	2
Stick	A10	Z	1	3

I want to make pivot table with calculated dimension such that locations 1,2 are grouped as NEW and locations X,Y,Z are OLD. Then, for parts in NEW I want to sum the "Lot Size" column and parts in OLD to sum the "Complete Qty" column. Like this:

	Part	Status	Sum
Bar	101 (TD ONLY)	NEW	2
Bar	102	OLD	7
Stick	A11 (TD ONLY)	NEW	2
Stick	A10	OLD	4

My dimension is this: =If(Match([Location],'X','Y','Z')>0,'OLD', If(Match([Location],'1','2') > 0,'NEW')) and my expression is this: =If(Match([Location],'X','Y','Z')>0,Sum([Complete Qty]), If(Match([Location],'1','2') > 0,SUM([Lot Size])).

But it doesn't work gives Null value for the Code "STICK" etc. How to achieve this? Please Help.

Re: Conditional Sum on Conditional Dimension

trdandamudi — Wed, 23 Jan 2019 21:34:19 GMT

May be you can do this in the script and it will be much easier like below:

Field1,

Field2,

If(Match([Location],'X','Y','Z')>0,'OLD',
If(Match([Location],'1','2') > 0,'NEW')) as Status

Now use the "Status" field in your chart along with the other dimensions. Hope this helps...

Re: Conditional Sum on Conditional Dimension

skompel2 — Thu, 24 Jan 2019 04:40:02 GMT

what should be in my expression to compute different sum?

Re: Conditional Sum on Conditional Dimension

trdandamudi — Thu, 24 Jan 2019 13:02:16 GMT

Below is the full code:

Data:
Load *,
If(Match([Location],'X','Y','Z')>0,'OLD',
If(Match([Location],'1','2') > 0,'NEW')) as Status;
Load * Inline [
Code, Part Desc, Location, Lot Size, Complete Qty
Bar, 101 (TD ONLY), 1, 1, 0
Bar, 102, X, 1, 5
Bar, 101 (TD ONLY), 2, 1, 0
Stick, A10, X, 1, 1
Stick, A11 (TD ONLY),2, 1, 0
Stick, A11 (TD ONLY),1, 1, 1
Bar, 102, Y, 1, 2
Stick, A10, Z, 1, 3
];

Chart Dimensions:

Code,

Part Desc

Status

Expression:

if(Status='OLD',Sum([Complete Qty]),
If(Status='NEW',SUM([Lot Size])))

Re: Conditional Sum on Conditional Dimension

sunny_talwar — Thu, 24 Jan 2019 13:15:21 GMT

Try this script

Table:
LOAD *,
	  If(Match([Location],'X','Y','Z'),'OLD',
	  	If(Match([Location], '1', '2'), 'NEW')) as Status;
LOAD * INLINE [
    Code, Part Desc, Location, Lot Size, Complete Qty
    Bar, 101 (TD ONLY), 1, 1, 0
    Bar, 102, X, 1, 5
    Bar, 101 (TD ONLY), 2, 1, 0
    Stick, A10, X, 1, 1
    Stick, A11 (TD ONLY), 2, 1, 0
    Stick, A11 (TD ONLY), 1, 1, 1
    Bar, 102, Y, 1, 2
    Stick, A10, Z, 1, 3
];

and then

Dimensions

Code
Part Desc
Status

Expression

=Pick(Match(Status, 'OLD', 'NEW'), Sum([Complete Qty]), Sum([Lot Size]))

Re: Conditional Sum on Conditional Dimension

skompel2 — Thu, 24 Jan 2019 17:11:05 GMT

Hi, I understood the script functionality. But I'm using this pivot table in a dashboard( by "create new sheet object") and my datasource is an excel file. The script editor already has all columns loaded in like this:

LOAD [Code],[Location],.....etc etc

FROM [C:\Users\a316109\Desktop\new.xlsx] (ooxml, embedded labels);

How can I add your script to the above preloaded script to get my pivot table?

Re: Conditional Sum on Conditional Dimension

sunny_talwar — Thu, 24 Jan 2019 17:21:57 GMT

Something like this

LOAD [Code],
[Location],
If(Match([Location],'X','Y','Z'),'OLD',
	  	If(Match([Location], '1', '2'), 'NEW')) as Status,
.....etc etc
FROM [C:\Users\a316109\Desktop\new.xlsx] (ooxml, embedded labels);

Re: Conditional Sum on Conditional Dimension

skompel2 — Thu, 24 Jan 2019 20:24:42 GMT

Thank you Sunny and Trdandamudi! Both your solutions work . But I found that we needn't use script editor, just use the "Add Calculated Dimensions" and put the label as "Status". Then use any of the Sum formula given above in "Expressions" otherwise it'll show Null value.
You guys are doing a wonderful work. 🙂

Re: Conditional Sum on Conditional Dimension

sunny_talwar — Thu, 24 Jan 2019 20:35:22 GMT

@skompel2 wrote:
Thank you Sunny and Trdandamudi! Both your solutions work . But I found that we needn't use script editor, just use the "Add Calculated Dimensions" and put the label as "Status". Then use any of the Sum formula given above in "Expressions" otherwise it'll show Null value.
You guys are doing a wonderful work. 🙂

You are right, you don't have to use Script to create a new field, but it is generally advisable to create a new field instead of using a calculated dimensions which are usually less efficient compared to a field when used as a dimension

Re: Conditional Sum on Conditional Dimension

vistab3st — Wed, 24 Jul 2019 08:10:56 GMT

I have an other issue some how similar,I have two sheets in one file with staff working hours.I need to combine both tables and figure each person's total working hour , and as weekly normal working hours is 40H extract how much they have overtime .

Full name (first and last name)	Monday 1st Jan	Tuesday 2nd Jan	Wednesday 3rd Jan	Thursday 4th Jan	Friday 5th Jan	Saturday 6th Jan	Sunday 7th Jan
John Smith	9:30:00	9:00:00	9:00:00	9:00:00	8:30:00
Lonna Morphy	7:20:00	9:30:00	10:15:00	9:00:00	10:20:00	0:00:00	0:00:00
Philip Fready	10:30:00	9:00:00	10:00:00	9:00:00	9:30:00	0:00:00	0:00:00
Mina Malik	10:30:00	9:00:00	12:00:00	9:00:00	9:30:00	0:00:00	0:00:00
Deniz Donmez	8:30:00	9:00:00	8:30:00	9:00:00	9:00:00	0:00:00	0:00:00
Gupta Kumar	9:15:00	10:00:00	9:30:00	9:15:00	9:00:00	0:00:00	0:00:00

Full name (first and last name)	Monday 8th Jan	Tuesday 9th Jan	Wednesday 10th Jan	Thursday 11th Jan	Friday 12th Jan	Saturday 13th Jan	Sunday 14th Jan
John Smith	8:00:00	8:00:00	8:00:00	8:00:00	8:00:00	0:00:00	0:00:00
Lonna Morphy	10:00:00	10:30:00	10:00:00	11:00:00	5:00:00	0:00:00	0:00:00
Philip Fready	10:00:00	10:00:00	8:00:00	8:00:00	8:00:00	0:00:00	0:00:00
Mina Malik	8:00:00	8:00:00	8:00:00	8:00:00	8:00:00	0:00:00	0:00:00