topic Re: Set Expression in pivot table in QlikView

Set Expression in pivot table

Wed, 24 Oct 2012 19:59:19 GMT

Hi all,

I have a little problem using a set expression in a pivot table:

in memory I just have a table with a lot of filelds, including Month and SaleAmount

I need a table with a dimesion (Month) and a few expressions (PreviousMonthSales, MonthSales, Percentage) that should look like this:

Month	PreviousMonthSales	MonthSales	Percentage
1	0	121	-
2	121	98	-19
3	98	75	-23
4	75	110	46
5	110	127	15
6	127	117	-7
7	117	82	-29
8	82	65	-20
9	65	89	36
10	89	101	13
11	101	97	-3
12	97	93	-4

While MonthSales is very easy, the problem is PreviousMonthSales, I tried with:

sum({<{Month={$(=avg(Month)-1)}>} TOTAL SaleAmount)

but it don't works. Could please someone explain me why and how can I accomplish this?

Thank you in advance

Carlo A. Babini

swuehl — Wed, 24 Oct 2012 20:03:51 GMT

Look into the chart inter record functions like above() function. [With pivoted dimensions to the top, you might need to use before(), though ]

=above(sum(Sales))

=before(sum(Sales))

for PreviousMonthSales.

Regards,

Stefan

Thu, 25 Oct 2012 12:29:42 GMT

Wow! I didn't knew about this, I also read about the other functions inter record and... what a powerful toolset!

Thank you so much

Carlo A. Babini

Month	PreviousMonthSales	MonthSales	Percentage
1	0	121	-
2	121	98	-19
3	98	75	-23
4	75	110	46
5	110	127	15
6	127	117	-7
7	117	82	-29
8	82	65	-20
9	65	89	36
10	89	101	13
11	101	97	-3
12	97	93	-4

Month	PreviousMonthSales	MonthSales	Percentage
1	0	121	-
2	121	98	-19
3	98	75	-23
4	75	110	46
5	110	127	15
6	127	117	-7
7	117	82	-29
8	82	65	-20
9	65	89	36
10	89	101	13
11	101	97	-3
12	97	93	-4

Month	PreviousMonthSales	MonthSales	Percentage
1	0	121	-
2	121	98	-19
3	98	75	-23
4	75	110	46
5	110	127	15
6	127	117	-7
7	117	82	-29
8	82	65	-20
9	65	89	36
10	89	101	13
11	101	97	-3
12	97	93	-4