Aggregation in pivot table in qliksense

Ric75 — Mon, 15 Jan 2024 10:27:53 GMT

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

Re: Aggregation in pivot table in qliksense

TcnCunha_M — Mon, 15 Jan 2024 10:15:41 GMT

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

Re: Aggregation in pivot table in qliksense

Ric75 — Mon, 15 Jan 2024 10:34:30 GMT

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

I've added a data set sample

topic Aggregation in pivot table in qliksense in App Development

Aggregation in pivot table in qliksense

Re: Aggregation in pivot table in qliksense

Re: Aggregation in pivot table in qliksense