Need help in getting expressions for pivot table

[#1]

Dear Qlikview Masters,

I am brand new to this tool and stuck in below items -

Overview-
Trying to create pivot table in qlikview to display departments attributes and fact values for resource type (FTE, Consultants,temps ext) for department groups.(Attached image)

I am having challenges in calculating below - >>>

1) Site

Site is a field associated to employee in database. Now , i need to display the site at department level ( means as a dimension ) which is the site for maximun number of FTE's ( For eg. If in a department , there are 20 Full time employees and out of that maximum are in kensas , then i need to display that site at department level) Tried couple of ways but no luck !!

2) Ratio

Total in above pivot table is actually a calculated dimension expression which shows the total number of resources for each department. Note that Total for department will not be changed at all. means not included in qlikview associated functionality.  Group column gives the group total means sum of FTE + Consultants + Temps. Now ratio is - Group / Total. Challenge is in calculating this value , because toatl in denominator is getting changed based on group. Objective is find the ratio of each group in the department. Stuck in getting the right expression

3) Common

My last challenge is to get the expression to find the common FTEs for each group. Means if Emp 1 , Emp 2 are common in Group1 , Group 2 , i should have expression called as Common, which will display 2.

I will really appreciate any help or guidance for above.

[MayilVahanan]

HI

1. Use if(rank(Site)=1,Site) as dimension

2. I think, not change in denominator value , if so, use sum(sales)/sum(total sales) or sum(sales)/sum({1}sales)

3. Group1 and Group2 have link? if so, use if(Group1=Group2,count(distinct Group1))

Hope it helps

[#3]

Thanks for the resonse but i wish the answer could be that easy.

I got the solution for two problems

1) Site - I have to use SQL appraoch & create query in load script to get the site for max resources in department

2) To get the ratio, i used following expression ( as i need calculation at row/ dimension)

sum(total <Name> aggr( Count({1} distinct [Emp Num]), Name ))

3) I am still finding difficult to get teh common count of employees between the selected group , ( selection of group is generic)

I have created variable as -

vSET = 'p({<Group Name={' & concat(distinct Group Name,'}>})*p({<Group Name={') & '}>})'

and using it in my expression

= count({<[Emp Num]=$(vSET) >}  distinct [Emp Num])

but it is not giving me correct resuilt. Any idea whats wrong

[#4]

Any suggestion or guidance on above expression please?  Thank you !!