Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi folks,
I have this pivot table which i am trying to integrate in it some kind of labels for some expressions. For that, i am unsuccesfully trying to generate some horizontal dimensions, but i am getting trouble when determining what expressions should be down each label (or, in this case, dimension).
Here is the print of i am trying to achieve (here, Volume, Efetivo, Total Vs and FAT vs are not-so-well aligned text objects).
Is it possible to do?
I have not prepared any helping file, but if any of you guys need it, let me now and i'll get one done.
Thanks!
Looking to get this?
Script:
Inline:
LOAD * Inline [
Name, Sales, Country, Amount, Product
Fernando, 500, BR, 2
Ademir, 200, BR, 3
Claudio, 100, USA, 4
William, 300, ARG, 1
Fernando, 200, BR, 3
];
Dim1:
LOAD * Inline [
Dim1
1
2
];
Dim2:
LOAD * Inline [
Dim2
1
2
3
4
5
];
Dimension:
Name
=Pick(Dim1, 'General Numbers', 'KPIs')
=Pick(Dim2, 'Sales', 'Amount', 'Orders', 'Sales%', 'Sales per product')
Expression:
=If(Dim1 = 1,
Pick(Dim2, Sum(Sales), Sum(Amount), Count(Name)),
Pick(Dim2, Null(), Null(), Null(), Num(Sum(Sales)/Sum(TOTAL Sales), '##,%'), Sum(Sales)/Sum(Amount)))
A sample would definitely help us understand what you are trying to do here.
Hi Sunny, thanks for your help.
Here is a sample, hope i could make myself clear over what i am trying to achieve.
Thanks again.
Looking to get this?
Script:
Inline:
LOAD * Inline [
Name, Sales, Country, Amount, Product
Fernando, 500, BR, 2
Ademir, 200, BR, 3
Claudio, 100, USA, 4
William, 300, ARG, 1
Fernando, 200, BR, 3
];
Dim1:
LOAD * Inline [
Dim1
1
2
];
Dim2:
LOAD * Inline [
Dim2
1
2
3
4
5
];
Dimension:
Name
=Pick(Dim1, 'General Numbers', 'KPIs')
=Pick(Dim2, 'Sales', 'Amount', 'Orders', 'Sales%', 'Sales per product')
Expression:
=If(Dim1 = 1,
Pick(Dim2, Sum(Sales), Sum(Amount), Count(Name)),
Pick(Dim2, Null(), Null(), Null(), Num(Sum(Sales)/Sum(TOTAL Sales), '##,%'), Sum(Sales)/Sum(Amount)))
Exactly what i was looking for.
Thanks a lot, Sunny!