Data Manipulation (Switch Measure for Dimension)

[akpofureenughwu]

Hello everyone.. I have a set of data in this format...

Location	Year	Quarter	UA	UC	RTA
Area 1	2015	Q1	12	1	2
Area 2	2015	Q1	13	1	0
Area 1	2015	Q2	34	2	1
Area 2	2015	Q2	13	2	1
Area 1	2015	Q3	13	1	0
Area 2	2015	Q3	1	2	0

The above table is the Health ,safety and Environment stats for a store with two branches....

I need to represent the data in a pyramid form and a bar chart form.. see attached for clarity..

I don't know how to manipulate the data (using UA, UC as dimension and Quarter as Measures)  see picture

thanks

[Digvijay_Singh · Accepted solution]

Check this may be -

[Anonymous]

yes off course, crosstable load is also an option... but the table must be in a specific format as we know

TEMP:
LOAD * INLINE [
Location, Year, Quarter, UA, UC, RTA
Area 1, 2015, Q1, 12, 1, 2
Area 2, 2015, Q1, 13, 1, 0
Area 1, 2015, Q2, 34, 2, 1
Area 2, 2015, Q2, 13, 2, 1
Area 1, 2015, Q3, 13, 1, 0
Area 2, 2015, Q3, 1, 2, 0
];


FINAL:
CrossTable(Type, Value, 3)
LOAD Location,
Quarter,
Year,
RTA,
UA,
UC
Resident TEMP;

DROP Table TEMP;

[Digvijay_Singh]

Yup, I agree.

[akpofureenughwu]

This is great with the use of crosstable I have managed to transfrom 4,000 lines of data ( automatically too)..  but I need to sort my data from (highest to lowest).. used expression.....

Pick(Match(ValueList('UA','UC','RTA'),'UA','UC','RTA'),Sum({<Key={'UA'}>}Value),Sum({<Key={'UC'}>}Value),Sum({<Key={'RTA'}>}Value))

but there was not change?

Thanks