topic Dividing two sums in QlikView

Dividing two sums

Tue, 19 Oct 2010 13:16:29 GMT

Hi,

I am trying to sum a series of numbers and divide the total by the total sum of a different series of numbers. The expression I'm using is giving me an incorrect total.

The expression I'm using is as below:

*************************************************************************************************************************************************

Sum({<Registration_Code = {'2'}, Vehicle_Status = {'Normal'}, Contract_Prefix = {'A'}, Salvage_Processed = {'1'}, YMDate = DisplayYM} [Total_proceeds])

Sum

Sum({<Registration_Code = {'2'}, Vehicle_Status = {'Normal'}, Contract_Prefix = {'A'}, Salvage_Processed = {'1'}, YMDate = DisplayYM} Market_Value)

*************************************************************************************************************************************************

I would really appreciate any assistance with this.

Regards,

Dividing two sums

Anonymous — Tue, 19 Oct 2010 13:35:41 GMT

S,
Nothing's wrong about the expression itself. Given the set conditions are the same, it is sum([Total_proceeds])/sum(Market_Value). It returns a mesure in %. Now, what is your definition of this expression total? It depends on your object properties. Is it sum of rows, or avg of rows, or expression total, or anything else? I assume you only need to use the correct total mode - which I cannot guess for you.

Dividing two sums

Wed, 20 Oct 2010 07:19:41 GMT

Michael, thanks for the response. Would you mind if I post a simplified example that you could look at. I've tried different 'of rows' properties such as average and sum. tx, S

Dividing two sums

Wed, 20 Oct 2010 08:36:38 GMT

Hi,

Please share sample data / QV file

Dividing two sums

Wed, 20 Oct 2010 09:11:35 GMT

Thanks Ravi, attached. The correct total should be 46.76%. tx, S

Dividing two sums

Wed, 20 Oct 2010 11:12:00 GMT

Hi Ravi,

I'm just curious as to whether you were able to figure out where I was going wrong after looking at the sample? It's such a stupid problem but it's been holding me up for a while now.

thanks,

Shane

Dividing two sums

Wed, 20 Oct 2010 12:00:07 GMT

Actually your current expression is calculating correctly. If you take out the two dimensions seperately and make a simple sum after you have chosen the same criteria as in your formula the sum calculates to

R 279495909,75

and

R 601890761,43

which then gives

0,464363183

I think you might have taken out the rows in Excel and summed them but the "Excel number" is not correct as rows are doubled. See enclosed.