Announcements
cancel
Showing results for
Did you mean:
Contributor II

## Conditional Sum on Conditional Dimension

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.

Labels (4)

• ### Dimensions

2 Solutions

Accepted Solutions
MVP

Try this script

```Table:
If(Match([Location],'X','Y','Z'),'OLD',
If(Match([Location], '1', '2'), 'NEW')) as Status;
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]))`

MVP

Something like this

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

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

Contributor II
Author

what should be in my expression to compute different sum?

Master II

Below is the full code:

Data:
If(Match([Location],'X','Y','Z')>0,'OLD',
If(Match([Location],'1','2') > 0,'NEW')) as Status;
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])))

MVP

Try this script

```Table:
If(Match([Location],'X','Y','Z'),'OLD',
If(Match([Location], '1', '2'), 'NEW')) as Status;
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]))`

Contributor II
Author

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:

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

MVP

Something like this

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

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

MVP

@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

Contributor

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
Community Browser