Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

manojkulkarni
Valued Contributor II

Pivot Table

Hi Friends,

Below is the sample data which i need to convert to pivot in expected format. Have attached both the details. How can we achieve this ?

Any help will be appreciated.

Input Data:

 

REGIONCityValue
EastBhubaneshwar22.9
EastGuwahati1.9
EastKolkotta1.9
EastPatna5.4
NorthChandigarh2.4
NorthDelhi3.8
NorthLucknow2.4
SouthBangalore2.1
SouthChennai4.3
SouthHyderabad1.9
SouthTrivandrum1.6
WestAhemadabad2.1
WestBhopal1.8
WestMumbai1.8

Expected result

 

REGIONCityValue
EastBhubaneshwar22.9
EastGuwahati1.9
EastKolkotta1.9
EastPatna5.4
East Total 8.0
NorthChandigarh2.4
NorthDelhi3.8
NorthLucknow2.4
North Total 2.9
SouthBangalore2.1
SouthChennai4.3
SouthHyderabad1.9
SouthTrivandrum1.6
Total 2.5
WestAhemadabad2.1
WestBhopal1.8
WestMumbai1.8
Total 2.1
Total 3.6
1 Solution

Accepted Solutions
Highlighted
MVP
MVP

Re: Pivot Table

It seems that Totals are the avg for the region... Something like this?

Capture.PNG

Expression used:

=If(Dimensionality() = 2, Sum(Value), Avg(Value))

4 Replies
MVP
MVP

Re: Pivot Table

How are the totals getting calculated?

Highlighted
MVP
MVP

Re: Pivot Table

It seems that Totals are the avg for the region... Something like this?

Capture.PNG

Expression used:

=If(Dimensionality() = 2, Sum(Value), Avg(Value))

Re: Pivot Table

It looked that your totals are the average, right? Then you could use:

avg(Value)

or if you need to sum the value on the city-level with an aggr-function, like:

avg(aggr(sum(Value), City))

- Marcus

manojkulkarni
Valued Contributor II

Re: Pivot Table

Thanks Marcus

Community Browser