Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Show Pivot and Straight Table in One Table

Hi All,

Basically, I just need to break down Transfer into Answered and Abandoned for Department.

For example:

TFN 8002212321 from Service line received 281 calls, 22 out of those 281 calls are hang ups and 259 calls are transferred to Boat department. In this 259 transferred calls to Boat department, 112 calls are answered and 147 are abandoned.

Is there anyway to show/merge pivot and straight table like below? Or is there a better way to present these data, instead of showing one straight table and one pivot? I have attached sample data below as well.

Screenshot.png

Thank you for your input!

1 Solution

Accepted Solutions
sunny_talwar

May be this:

Capture.PNG

Script:


Table:

LOAD tfn_bu,

    toll_free_number,

    gen_vq_resource_name,

    session_key,

    hangup,

    transfer,

    answered,

    abandoned

FROM

[sample data.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Table1:

LOAD * Inline [

Dim

1

2

3

4

5

];

Pivot Table

Dimensions

1) tfn_bu

2) toll_free_number

3) =If(Match(Dim, 4, 5), gen_vq_resource_name, ' ')

4) =Pick(Dim, 'Total Calls', 'Hang Ups', 'Transfer', 'Answered', 'Abandoned')

Expression:

Pick(Dim,

RangeSum(Sum(hangup), Sum(transfer)),

Sum(hangup),

Sum(transfer),

Sum(answered),

Sum(abandoned))

View solution in original post

7 Replies
sunny_talwar

Excel seems to contain the output data. Where is the raw data which we can use to create the output you are looking for? Can you share that

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

Technically, you cannot merge a Straight Table and a Pivot table, these are two separate objects. However, you can build the desired presentation within a Pivot Table. For that, you need to convert any Expressions that should not be iterated over your pivoted dimension (Department), into calculated dimensions.

This can be done using the advanced aggregation function AGGR(). You should state your calculation and use your "main" chart dimensions as the AGGR dimensions. For example:

AGGR( COUNT(CallID) , [TFN BU], TFN)

Using this formula in a calculated dimension will cause this dimension to appear once per chart, next to the other dimensions.

I'm going to teach a technical session about the AGGR function at Qonnections, on Wednesday at 10:00 AM. Anyone who'd like to listen - it's not too late to reserve your seat!

cheers,

Oleg Troyansky

Check out my new book QlikView Your Business - The Expert Guide for QlikView and Qlik Sense

sunny_talwar

May be this:

Capture.PNG

Script:


Table:

LOAD tfn_bu,

    toll_free_number,

    gen_vq_resource_name,

    session_key,

    hangup,

    transfer,

    answered,

    abandoned

FROM

[sample data.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Table1:

LOAD * Inline [

Dim

1

2

3

4

5

];

Pivot Table

Dimensions

1) tfn_bu

2) toll_free_number

3) =If(Match(Dim, 4, 5), gen_vq_resource_name, ' ')

4) =Pick(Dim, 'Total Calls', 'Hang Ups', 'Transfer', 'Answered', 'Abandoned')

Expression:

Pick(Dim,

RangeSum(Sum(hangup), Sum(transfer)),

Sum(hangup),

Sum(transfer),

Sum(answered),

Sum(abandoned))

Not applicable
Author

Hi Oleg,

Thank you for your explaination!!

Not applicable
Author

Hi Sunny,

It's exactly what I need! Thank you very much.

I was able to use your sample and solve my problem. I have another question for you.

So I add more metrics and some of them need to show in percentage and integer. There is only one expression for all metrics, therefore I can't format the metrics separately. How do I do this?

Thanks again!

sunny_talwar

You can add an if statement based on the dimension and set different format types using Num() function.

Not applicable
Author

Thanks again!