topic Re: Return calculated value from pivot for set dimension in QlikView

Return calculated value from pivot for set dimension

Tue, 30 Sep 2014 15:03:31 GMT

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
1	M	50	50
2	M	70	70
3	F	80	90
3	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

rubenmarin — Tue, 30 Sep 2014 15:08:30 GMT

Hi Matt,

The expression for expected result should be something like:

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

Re: Return calculated value from pivot for set dimension

Tue, 30 Sep 2014 16:17:12 GMT

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

JonnyPoole — Tue, 30 Sep 2014 16:22:58 GMT

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

Tue, 30 Sep 2014 19:39:38 GMT

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

JonnyPoole — Tue, 30 Sep 2014 20:05:15 GMT

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

Tue, 30 Sep 2014 20:51:46 GMT

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!

Re: Return calculated value from pivot for set dimension

JonnyPoole — Tue, 30 Sep 2014 20:56:13 GMT

Great. Kindly close out the thread with the correct/helpful messages.