7 Replies Latest reply: Sep 30, 2014 4:56 PM by Jonathan Poole

# Return calculated value from pivot for set dimension

I am trying to return the results of a calculated value while looking at one set dimension for a pivot. For example I have a pivot looking at the Grade and Gender of a person and want to return the results of only a Gender of M regardless of what the actual Gender is of the line. I have an example of my data below, the expected result always returns the avg rate of the Grade for the M Gender.

 Grade Gender Avg Rate Expected Result 1 F 60 50 M 50 50 2 M 70 70 3 F 80 90 M 90 90

I have tried many different expressions but cannot get any to consistently return the right value.

Thanks,
Matt

• ###### Re: Return calculated value from pivot for set dimension

Hi Matt,

The expression for expected result should be something like:

Avg({<Gender={'M'}>} Rate)

• ###### Re: Return calculated value from pivot for set dimension

That is only adding the average to the row with the M Gender and a 0 for all rows with a F Gender.

• ###### Re: Return calculated value from pivot for set dimension

maybe this. but i'd feel better if you post the raw data or the qvw

if(

Gender = 'M',    sum(  [Avg Rate] ) ,

if ( Gender = 'F', sum ( {\$<Gender={'M'}>} [Avg Rate] ) )

)

• ###### Re: Return calculated value from pivot for set dimension

Jonathan,

Thanks for your help but that did not work either. Sorry, I am new and do not know how to upload my data.

• ###### Re: Return calculated value from pivot for set dimension

See if this will work:

if ( Gender='F',  sum(  total <Grade> [Avg Rate]) - sum([Avg Rate]) , sum([Avg Rate]))

I tried it with the few rows of data you had in your first post.

Otherwise, when you reply, go to 'Use Advanced Editor' and then you can attach QVW files or data files.

• ###### Re: Return calculated value from pivot for set dimension

Jonathan,

That worked with a slight modification. Below is what I used:

if ( Gender='F',  (sum(  total <Grade> [Avg Rt]) - sum([Avg Rt]))/(count(total<Grade> [Unique ID]) - count( total <Grade,Gender> [Unique ID])) , avg([Avg Rt]))

Unique ID was just another field I had but probably could have used any field in place for that count.

Your formula worked if there were an equal number of people by gender. I was able to get the counts of each gender so that the sum total was divided by the number of 'M' to get the average for the 'M'. This may have been taking a longer way than needed to solve the problem but it worked.

Thank you very much for your help! Greatly Appreciated!