Dimensions in pivot table

[#1]

Hey guys,

I'm fairly new to QlikView and currently struggle with a seemingly easy issue.

I have something like this:

                    2001               2002

Persons          10                  100

and a pivot table that entirely closed looks like this:

Group	Year	2001	2002
Group A	Sum Costs	100	105
	Costs p. Person	0.91	0.95
Group B	Sum Costs	120	45
	Percentage	1.10	0.41

But I want the table to look like this:

Group	Year	2001	2002
Group A	Sum Costs	100	105
	Costs p. Person	10	1.05
Group B	Sum Costs	120	45
	Costs p. Person	12	0.43

Problem:

The costs per person are calculated using sum of all persons (110) not the amount of people per year (10 respectively 100).

Question:

How can I match the number of persons with the dimension (year) of the pivot table?

It'd be great if someone has a solution for me! Thanks in advance!

[#2]

Hi Prakti123,

Try using set analysis in Costs per Person Calculation for taking Year into Consideration. Hope this helps.

[mazacini]

I think you need to give more details about your data model.

If you have a cost table with Group, Year and Cost and a persons table with Year and Persons, it should be easy to produce the desired result.

However, if your cost and persons data is contained in the same table, you might need a different approach.

[#4]

Hey guys, thanks for your answers and great tipp with the set analysis in the formula! I'm really close to the result, I want to achieve.

I first tried to calculate the number of persons for each year again (because this changes with the selection made):

COUNT({[MyAlternateState]<Year>} DISTINCT Persons)

Now this works just fine, but the problem I still have is that I don't want the number of people matched with the dimension Group, because it's supposed to be an average over the entire population per year, not just the population that is connected to the specific Group.

Is there a way to eliminate the differentiation for Group out of my set analysis?

Hope this is somewhat clear! If not just let me know, what information you need!

Thanks for the great help

[#5]

If you want to eliminate consideration of Group from your set analysis you should use

eg: COUNT({[MyAlternateState]<Year={"[values]"},Group=>} DISTINCT Persons)

"Group =" will ignore the dimension Group in set analysis. Please mark this thread answered if this solution solves your problem.

[#6]

Thanks for your reply. Unfortunately excluding the dimension Group (and the subdimensions in the Pivot table for each group) the way ypou proposed doesn't work. Maybe it is because of the nature of a pivot table?

Do you have any further advice? ;D