topic Re: Needing quotient of sums of two expressions in QlikView

Needing quotient of sums of two expressions

triciagdaly — Mon, 16 Mar 2015 14:51:34 GMT

I am needing a total for Overall PPV % that is quotient of using sums of two expressions. Sum(Ext Variance Amt/PPV) / Sum(Ext Price Paid), but not sure how to accomplish this. The individual Overall % on the detail lines works fine - Ext Variance Amt/PPV / Ext Price Paid. In the example attached, using Ext Variance Amt/PPV total of 165,252.01 and Ext Price Paid total of 2,034,568.83, the Overall PPV % should be 7.17%. Any help would greatly be appreciated. Also I have a screen shot attached for reference.

Re: Needing quotient of sums of two expressions

hic — Mon, 16 Mar 2015 15:25:56 GMT

You need to think through your formulas a little...

You mix aggregated numbers with unaggregated, and then you will get problems like this.

For instance, in the image above, the [Unburdened Cost] is a reference to another measure, hence it is an aggregated number. But the RECEIPT_PRICE and the quantity are naked (not wrapped in aggregation functions) so they cannot always be evaluated - especially not on the total row.

You need to wrap these in aggregation functions. E.g. by

=([Unburdened Cost] - Avg(RECEIPT_PRICE))*Sum(fabs(RECEIPT_APPROVED_QUANTITY))

See more on Use Aggregation Functions!

HIC

Re: Needing quotient of sums of two expressions

triciagdaly — Mon, 16 Mar 2015 16:19:26 GMT

Ok. That makes sense. The ultimate objective is the quotient of the sum of each of the below divisors. I will probably need to use aggr, just not very clear on syntax.

([Unburdened Cost] - Avg(RECEIPT_PRICE))*Sum(fabs(RECEIPT_APPROVED_QUANTITY)) / sum(RECEIPT_AMOUNT)

The sum of expression in red / sum of expression in blue is what I’m needing. Any help is appreciated!

Re: Needing quotient of sums of two expressions

hic — Mon, 16 Mar 2015 17:59:12 GMT

I don't think you need Aggr(). I would go for exactly the formula you've written. However, you will probably need to correct the expression that defines [Unburdened Cost] also (and possibly also the other expressions) since also this contains naked field references. Then I think it will work.

HIC