Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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]))
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);
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...
what should be in my expression to compute different sum?
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])))
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]))
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?
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);
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. 🙂
@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
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 |