topic Re: Pivot table, Aggr- help needed in QlikView

Pivot table, Aggr- help needed

Tue, 31 May 2016 18:09:34 GMT

!Hello,

creating a pivot table with 2 dimensions (Dim1 and Dim2) and trying to create an expression where

it should be like

Aggr(count( Total Distinct [FieldA]),Dim1) - the aggr needs to be calculated on Dim1 only but the pivot table has Dim2 which shouldn't be included in calculating the expression.

Thanks.

Re: Pivot table, Aggr- help needed

sunny_talwar — Tue, 31 May 2016 18:11:38 GMT

Try this:

Count(TOTAL <Dim1> [FieldA])

Re: Pivot table, Aggr- help needed

Tue, 31 May 2016 18:44:13 GMT

I knew i would get the right help here!

But, i realized that my question needs to be more accurate for the right help.

When i tried with your solution it gave me a (19+21)40 across all thru REMC and RMCSE.

Please find the table to provide more idea on the post that i am looking to achieve.

Thanks.

Re: Pivot table, Aggr- help needed

sunny_talwar — Tue, 31 May 2016 18:46:40 GMT

Table? Did you attach something?

Re: Pivot table, Aggr- help needed

Tue, 31 May 2016 18:49:25 GMT

Sorry,For some reason it didn't show up earlier, just re posted again.

Re: Pivot table, Aggr- help needed

sunny_talwar — Tue, 31 May 2016 18:51:42 GMT

I still think you can do this using this:

Count(TOTAL <Dim1> [FieldA])

Is this not working?

Re: Pivot table, Aggr- help needed

Tue, 31 May 2016 18:56:42 GMT

With the Count(TOTAL <Dim1> [FieldA]),

I am getting a value 40 across Dim1-REMC and RMCSE.

But the anticipation is that it should be 19 for REMC across and 21 for RMCSE across.

Dim1	Dim2	Exp1
REMC	N/s 1c2	40
REMC	RWD	40
REMC	TESC	40
RMCSE	NS 2.1	40
RMCSE	RC 1.2	40
RMCSE	N/s 1c2	40

Re: Pivot table, Aggr- help needed

sunny_talwar — Tue, 31 May 2016 19:01:13 GMT

You have Both Dim1 and Dim2 as dimensions right and not just Dim2? If you have just Dim2 as dimension, then try this:

Sum(Aggr(Count(TOTAL <Dim1> [FieldA]), Dim1, Dim2))

Re: Pivot table, Aggr- help needed

Tue, 31 May 2016 20:19:06 GMT

Hello Sunny,

There are Dim1 and Dim2 as dimensions in the pivot table and if try the expression created it does the aggr but again shows the aggr of overall Dim1 but need a dim1 values RMCSE and REMC.

looking for a which will show in the exp across for all Dim1 REMC it should 19 only same with the RMCSE it should be 21 static for all Dim2 values.

working on creating an example qvw that i can post for more info.

Thanks.

Re: Pivot table, Aggr- help needed

sunny_talwar — Tue, 31 May 2016 20:23:14 GMT

For demonstration created a sample. Check it out:

NOTE: Used Sum instead of Count, but you might need Count.

Re: Pivot table, Aggr- help needed

Tue, 31 May 2016 20:54:05 GMT

Hello Sunny,

Please find the qvw file with reduced data, on the original post.

The desired final end result should be below numbers irrespective of Dim2 in the pivot table.

But with your expression i was getting 3298 which the number 3298 should break out by Dim1 values as shown below.

ER	46
MR	1160
SR	520
SRM	797
WR	375

Thanks.

Re: Pivot table, Aggr- help needed

sunny_talwar — Tue, 31 May 2016 20:58:45 GMT

This?

Count(DISTINCT TOTAL <Dim1> FAC_NUM)

Re: Pivot table, Aggr- help needed

Tue, 31 May 2016 21:07:13 GMT

Well, all along i was missing to move the distinct and total around to get the required solution.

At the end i wouldn't need a Aggr after all .

Greatly appreciate your help.

Thanks.

Re: Pivot table, Aggr- help needed

sunny_talwar — Tue, 31 May 2016 21:11:14 GMT

It was not that: Here is the comparison:

Yours:

Count(TOTAL{<Dim1>} DISTINCT FAC_NUM)

Mine:

Count(DISTINCT TOTAL <Dim1> FAC_NUM)

Count(TOTAL <Dim1> DISTINCT FAC_NUM)

One is set analysis to ignore selection in Dim1 (Yours). Other is a field on which you want to TOTAL on (Mine)

Re: Pivot table, Aggr- help needed

Tue, 31 May 2016 21:17:54 GMT

Thanks for the clarification Sunny.

Re: Pivot table, Aggr- help needed

sunny_talwar — Tue, 31 May 2016 21:19:05 GMT

No problem at all. I am glad I was able to offer help.

Best,

Sunny