Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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

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

Re: Pivot Table

How are the totals getting calculated?

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

Highlighted
manojkulkarni
Valued Contributor II

Re: Pivot Table

Thanks Marcus

Community Browser