topic Re: Show Pivot and Straight Table in One Table in QlikView

Show Pivot and Straight Table in One Table

Wed, 25 Nov 2020 16:16:04 GMT

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.

Thank you for your input!

Re: Show Pivot and Straight Table in One Table

sunny_talwar — Thu, 28 Apr 2016 16:38:18 GMT

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

Re: Show Pivot and Straight Table in One Table

Oleg_Troyansky — Thu, 28 Apr 2016 17:12:46 GMT

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

Re: Show Pivot and Straight Table in One Table

sunny_talwar — Fri, 29 Apr 2016 21:47:02 GMT

May be this:

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

];

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))

Re: Show Pivot and Straight Table in One Table

Tue, 03 May 2016 15:01:38 GMT

Hi Oleg,

Thank you for your explaination!!

Re: Show Pivot and Straight Table in One Table

Tue, 03 May 2016 15:07:51 GMT

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!

Re: Show Pivot and Straight Table in One Table

sunny_talwar — Tue, 03 May 2016 15:23:47 GMT

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

Re: Show Pivot and Straight Table in One Table

Tue, 03 May 2016 15:50:08 GMT

Thanks again!