How to display Total Values in Pivot irrelevant of Dimension

[#1]

Hi All,

I have a following Query ...

I have a dimension named Occupancy . Occupancy consists of following values Subject1 , Subject2 , Subject3 ,Subject4 .

The total count of students for Subject1 + Subject2 +Subject3 + Subject4 =100.

But in the dimension I have to consider only subject1 and Subject2 as shown in the below table

In the Pivot Table I have to display the results as below

Year	Occupancy	Count of Students	Total Students	%
2010	Subject 1	30	100	30%
	Subject 2	40	100	40%

The problem here is as I am taking only the Subject1 & Subject2 in the Dimensions . The data is also reduced to that level and I am unable to get the Total Students as 100.

How to display the total Students as 100 . Kindly help me to resolve the Total Students Column .

Thanks in Advance......

[#2]

hi

U can do through set analysis

Regards,

Suman

[#3]

Hi,

You can use aggr function in order to represent your expression against some other dimension.

aggr(exp,dimension)

Regards,

Vikas Kumar

[#4]

Hi ,

Can u please suggest how to do it using Set Analysis ...

Regards,

Chakravarthy

[#5]

Hi Vikas,

I have tried aggregate function but unable to achieve it .

Can u share an example ...

Regards,

Chakravarthy.

[#6]

Hi,

As u have written u want total irrespective of Dimensions i think you can use function Total.

Like: Sum(total Sales)

Regards,

Ravi

[#7]

Hi Ravi,

Thanks for your reply.

If I use the Total It gives the Total as a whole irrelevant to the Year ... and also I have few more dimensions in my table .

Regards,

Suman.

[johnw]

Perhaps this?

if(column(1),count({<Occupancy=>} total <Year> distinct Student))

See attached. If that's not what you want, it would make it easier to solve your problem if you posted an example of it or modified mine to suit.

[#9]

Hi John ,

Thanks for your reply. But it did not solved my problem . I want to explain it in detail . The following table represent my data .

Year	Group	Relative	Occupancy	No. of Participants
2009	Finance	Related	Subject1	1
2009	Finance	Related	Subject2	32
2009	Finance	Related	Subject3	63
2009	Finance	Related	Subject4	24
2009	Finance	Total		120
2009	HR	Related	Subject1	-
2009	HR	Related	Subject2	31
2009	HR	Related	Subject3	43
2009	HR	Related	Subject4	14
2009	HR	Total		88
2009	Sales	Related	Subject1	1
2009	Sales	Related	Subject2	26
2009	Sales	Related	Subject3	43
2009	Sales	Related	Subject4	8
2009	Sales	Total		78

I want the output as the following table . I have used the IF Condition at the back end to get only Subject2 & Subject3 in Occupancy Column.

Year	Group	Relative	Occupancy	No. of Participants	Total Participants	%
2009	Finance	Related	Subject2	32	120	26.7
2009	Finance	Related	Subject3	63	120	52.5
2009	HR	Related	Subject2	31	88	35.2
2009	HR	Related	Subject3	43	88	48.9
2009	Sales	Related	Subject2	26	78	33.3
2009	Sales	Related	Subject3	43	78	55.1

Total Participants column is my requirement .I have get the sum of all the subjects with respective of dimnesions .

Please help me on the same .

Thanks in Advance.....

Regards,

Chakravarthy.

[#10]

Hi ,

Find the attached QVW..