Aggregation in pivot table in qliksense

[Ric75]

Hi,

I’ve some issues in calculating a % rate between 2 measures with different dimensions. Here is my data structure:

TABLE 1	Note
Dimension_A	Integer
Dimension_B	Date (I use the year)
X	Field to count

TABLE 2	Note
Dimension_C	Date (I use the year)
Y	Field to count

What I’ve to do is to evaluate:

• COUNT(X) aggregate for dimension B and A
• COUNT (Y) aggregate for dimension C

		C1 (Dimension_C)	C2 (Dimension_C)	C3 (Dimension_C)
A1 (Dimension_A)	B1 (Dimension_B)	COUNT(Y)/COUNT(X)	COUNT(Y)/COUNT(X)	COUNT(Y)/COUNT(X)
	B2 (Dimension_B)	COUNT(Y)/COUNT(X)	COUNT(Y)/COUNT(X)	COUNT(Y)/COUNT(X)
	B3 (Dimension_B)	COUNT(Y)/COUNT(X)	COUNT(Y)/COUNT(X)	COUNT(Y)/COUNT(X)
A2 (Dimension_A)	B1 (Dimension_B)	COUNT(Y)/COUNT(X)	COUNT(Y)/COUNT(X)	COUNT(Y)/COUNT(X)
	B2 (Dimension_B)	COUNT(Y)/COUNT(X)	COUNT(Y)/COUNT(X)	COUNT(Y)/COUNT(X)
	B3 (Dimension_B)	COUNT(Y)/COUNT(X)	COUNT(Y)/COUNT(X)	COUNT(Y)/COUNT(X)

My problem is to evaluate the measure COUNT(X) ignoring Dimension_C.

This is what I’ve tried so far:

• sum(aggr(count(X), Dimension_B, Dimension_A))
• sum(aggr(count(total < Dimension_B, Dimension_A > X), Dimension_B, Dimension_A))
• sum(aggr(count({<Dimension_C=>} total < Dimension_B, Dimension_A > X), Dimension_B, Dimension_A))

They all produce the correct count(X) value in just one Dimension_C column and 0 in the others, while if I use:

• sum(total < Dimension_B, Dimension_A > aggr(Count(X), < Dimension_B, Dimension_A))

I’ve a wrong count(X) but the same number for all the columns

For Dimension_B and Dimension_C, since I need the year I’ve tried to use either [Dimension_B.Autocalendar.Year] or to extract the year in the loading script and use it as an integer.

Can someone help me?

Thanks a lot,

Riccardo

[TcnCunha_M]

could tell me why you are doing sum(aggr(count(X), Dimension_B, Dimension_A))
not count(aggr(count(X), Dimension_B, Dimension_A)) ?

I believe if you are counting and aggregating something you should finish counting what are you trying to
aggregate 

also will be good if could show some sample data so we can try to simulate the issue

[Ric75]

In my experience not always but maybe it's why doesn't work.

I've added a data set sample