topic Pivot Table in QlikView

Pivot Table

Tue, 28 Jul 2015 05:23:55 GMT

Hi All,

I am having a Pivot Table with Location,Name & Date as Dimension and Sum of a column Handled as expression.

Output is as given below.

Location	Name	Date	2015-07-01	2015-07-02	2015-07-03	2015-07-04	2015-07-05
Bangalore	Agent 1		-	21	-	-	-
Bangalore	Agent 2		491	1473	1104	-	-
Bangalore	Agent 3		-	-	-	-	-
Bangalore	Agent 4		500	653	488	-	-
Bangalore	Agent 5		745	758	765	-	-

Need to add following columns at teh end.

Avg Per Day

Max

Min

# of days with > 500

How do i add this to the Pivot table as last columns post teh dates.

Re: Pivot Table

MK_QSL — Tue, 28 Jul 2015 05:48:05 GMT

Please provide sample data or sample application. You need to use TOTAL Qualifier to achieve this result.

Re: Pivot Table

PrashantSangle — Tue, 28 Jul 2015 06:08:12 GMT

Hi,

If Date is also your dimension then i dont think so it is possible to do required thing in Pivot table.

You can try in Straight Table by creating each date value as different Expression.

Regards

Re: Pivot Table

Tue, 28 Jul 2015 06:09:13 GMT

Date	Handled	Name
2015-07-01	267	Agent 1
2015-07-02	254	Agent 2
2015-07-02	321	Agent 3
2015-07-02	330	Agent 4
2015-07-03	396	Agent 5
2015-07-04	258	Agent 6
2015-07-06	253	Agent 7
2015-07-06	315	Agent 8
2015-07-06	319	Agent 9
2015-07-07	254	Agent 10
2015-07-07	294	Agent 11
2015-07-08	252	Agent 1
2015-07-08	253	Agent 2
2015-07-08	271	Agent 3
2015-07-08	361	Agent 4
2015-07-09	286	Agent 5
2015-07-10	385	Agent 6
2015-07-13	260	Agent 7
2015-07-16	260	Agent 8
2015-07-17	306	Agent 9
2015-07-17	315	Agent 10
2015-07-17	366	Agent 11
2015-07-20	253	Agent 1
2015-07-20	272	Agent 2
2015-07-20	311	Agent 3
2015-07-20	347	Agent 4
2015-07-22	297	Agent 5
2015-07-23	253	Agent 6
2015-07-23	309	Agent 7
2015-07-23	463	Agent 8
2015-07-24	266	Agent 9
2015-07-25	272	Agent 10
2015-07-26	253	Agent 11
2015-07-26	291	Agent 1
2015-07-26	335	Agent 2
2015-07-26	418	Agent 3

Please use the above sample data, Ignore location which i will add it later.

Re: Pivot Table

Tue, 28 Jul 2015 06:11:50 GMT

How do i change that dynamically.

Today i will have date till JUly 27th and tomorrow it will till 28th.

Do you mean i need to add expression default till 31st always.

Re: Pivot Table

PrashantSangle — Tue, 28 Jul 2015 06:22:52 GMT

Hi,

Yes,

Regards,

Re: Pivot Table

MK_QSL — Tue, 28 Jul 2015 06:25:23 GMT

Use Straight Table

For Average

=SUM(TOTAL <Name> Handled)/COUNT(TOTAL <Name> Date)

For Min

Min(TOTAL <Name> Aggr(SUM(Handled),Name,Date))

For Max

Max(TOTAL <Name> Aggr(SUM(Handled),Name,Date))

For >= 500

SUM(TOTAL <Name> Aggr(IF(SUM(Handled)>=500,1,0),Name,Date))

Re: Pivot Table

Tue, 28 Jul 2015 10:03:38 GMT

HI,

In this method for the dates when there is no data available for an Name it takes previous days data and shows the same in Table.

How to avoid it?

Re: Pivot Table

Tue, 28 Jul 2015 11:21:05 GMT

Sorry It was my mistake in the expression i had mentioned <=