Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a column category in load editor and I am using it as a row in pivot table as following-
1-3Saved
1-3 Posted
2-5 Saved
2-3 Posted
3-7 Saved
3-7 Posted
I want while appearing in a pivot the saved and posted should appear together.
Expected Output
1-3 Saved
2-5 Saved
3-7 Saved
1-3 Posted
2-3 Posted
3-7 Posted
Hi,
PFA, try to create calculated dimension or just keep this expression in your Category field Sort expression as below
=if(Category ='1-3Saved', Dual('1-3Saved',1),
if(Category='2-5 Saved', Dual('2-5 Saved',2),
if(Category='3-7 Saved', Dual('3-7 Saved',3),
if(Category='1-3 Posted', Dual('1-3 Posted',4),
if(Category='2-3 Posted', Dual('2-3 Posted',5),
if(Category='3-7 Posted', Dual('3-7 Posted',6)))))))
PS: best way try to push back end script (above if will work in back end load script and other chart object
Thanks,
Deva
In ur script, create a new field as follow:
load * ,text( replace( col,left(col,find-1),'')) as newField;
load *, FindOneOf("col", 'SP') as find;
load * Inline [
col
1-3Saved
1-3 Posted
2-5 Saved
2-3 Posted
3-7 Saved
3-7 Posted
];
drop field find;
and in ur table, sort by expression : len(newField)
You can take advantage of the Dual()-function to create a field that has both text and numeric values for each value:
[MYCAT]:
LOAD
Dual( Txt , Num ) AS MyCat
INLINE [
Txt, Num
1-3 Saved
2-5 Saved
3-7 Saved
1-3 Posted
2-3 Posted
3-7 Posted
];
if ur 1st value; I mean 1-3 , 1-5 ... the one that comes before Saved and Posted are always of a length of 3; u can directly sort by expression : Len(YourColumn) without creating the newField
Hi Petter,
I already have a category column like this in the database itself, I am not making this in the load editor
Hi Omar,
I tried to make a test column close to real data ,in the real data the length between numbers and letters varies.
Hi,
PFA, try to create calculated dimension or just keep this expression in your Category field Sort expression as below
=if(Category ='1-3Saved', Dual('1-3Saved',1),
if(Category='2-5 Saved', Dual('2-5 Saved',2),
if(Category='3-7 Saved', Dual('3-7 Saved',3),
if(Category='1-3 Posted', Dual('1-3 Posted',4),
if(Category='2-3 Posted', Dual('2-3 Posted',5),
if(Category='3-7 Posted', Dual('3-7 Posted',6)))))))
PS: best way try to push back end script (above if will work in back end load script and other chart object
Thanks,
Deva
Hi Devarasu,
Is it possible if I can apply colors to the category items in view while using pivot.
Hi,
u wanted to apply dimension field value or measure value? if measure value u can apply like below,
just add this expression inside your pivot table measure field - background/text Color expression
if(Category='1-3 Posted',green(),
if(Category='2-3 Posted',blue(),
if(Category='2-5 Saved',lightgreen(),
if(Category='3-7 Posted',lightblue(),Yellow()))))