Conditional Sum on Conditional Dimension

[skompel2]

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.

[trdandamudi]

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

[skompel2]

what should be in my expression to compute different sum?

[trdandamudi]

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

[sunny_talwar · Accepted solution]

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

[skompel2]

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?

[sunny_talwar · Accepted solution]

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

[skompel2]

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

[sunny_talwar]

---

@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

[vistab3st]

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