Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Community,
I've a requirement to display data not horizontally but vertically in a Pivot table.
I'm trying from my end but facing some issues. Need your expert help here.
I'm sharing sample data Excel file. PFA.
Original Data Source table and Pivot Table column dimensions:
Trying to achieve result with Valuelist Function:
Please help.
Regards,
Eric
Hi Eric,
if I understood correctly, you are facing difficulties when getting different profiles for each employee (as there could be multiple combinations for the same employee + state, etc.).
I believe this could be solved in the script using composite key + crosstable (see script bellow - using your input data):
Raw:
LOAD
Name &'|'& State &'|'& Salary &'|'& Status as %Employee_key, // composite key for different profiles
Name,
Emp_Id,
State,
Salary,
Status,
Date_Entry
FROM [lib://Desktop/Sample_Data - Community.xlsx]
(ooxml, embedded labels, table is Sheet1)
where Salary <> 0; // filter out zero values, can be adjusted as necessary
Tmp:
CrossTable(Dimension,Value)
LOAD
%Employee_key,
State,
Salary,
Status,
Date_Entry
resident
Raw
order by Name; // for nice ordering of employees in profile numbers
Final:
LOAD
subfield(%Employee_key,'|',1) as Name,
autonumber(%Employee_key) as Profile,
*
resident
Tmp;
drop table Tmp;
drop field %Employee_key;
Then in the front-end pivot object you can select:
Hope that helps.
Kind regards,
Tomas
Hi @kleman_emark,
Thanks for replying to my question. My requirement is very simple.
I just want to transpose Horizontal columns to vertical rows.
The change is required at front end level.
See the desired output:
Regards,
Eric
Add all your dimensions to Column as normal, just restrict all measures to only evaluate for employees with sum(Salary)>0
example
Measure for Salary
=if(sum(Salary)>0,sum(Salary)) OR sum({<Emp_Id={"sum(Salary)>0"}>}Salary)
Measure for Contract
=if(sum(Salary)>0,sum(Contract)) OR sum({<Emp_Id={"sum(Salary)>0"}>}Contract)
Measure for SomethingElse
=if(sum(Salary)>0,sum(SomethingElse)) OR sum({<Emp_Id={"sum(Salary)>0"}>}SomethingElse)
and so on
Then Under Add-Ons>> Data Handling>> Uncheck Include Zero values
Can anyone have solution to this requirement.
Have you tried this yet!
sum({<Emp_Id={"=sum(Salary)>0"}>}Salary)
sum({<Emp_Id={"=sum(Salary)>0"}>}Contract)