topic Re: Calculation on Totals row values help please.... in QlikView

Calculation on Totals row values help please....

Tue, 28 Apr 2015 16:55:05 GMT

I have a straight table.

A dimension column with Invoice $ values, another expression column calculating the Invoice $ value * a Days to payment expression.

Which basically gives me a weighted value of those $ invoiced.

I can throw up a Total row... giving me the TOTAL of the Invoice $, and the TOTAL of the $ Weighting.

What I'm trying to achieve in the end... is to have a value of the TOTAL of the $ Weighting / TOTAL of the Invoice Amount.

I can run this in an expression on a row by basis.. .which is basically just that rows "Days to payment" value... but I want to see the Overall DPO based on the TOTAL of those columns....

Not sure how to accomplish this... any suggestions would be appreciated.

Screen shot of straight table attached.

Re: Calculation on Totals row values help please....

Anonymous — Tue, 28 Apr 2015 17:04:49 GMT

It makes sense to calculate it out of the chart. For example in a text box, or better in the chart's window title.

Re: Calculation on Totals row values help please....

Tue, 28 Apr 2015 17:09:15 GMT

I’m unfortunately pretty novice and haven’t done either those options.

Re: Calculation on Totals row values help please....

Wed, 29 Apr 2015 15:53:05 GMT

I guess what I'm trying to determine...is how in the TEXT Object can I specify that I want to divide the Total value of the Invoice $ Weighting expression's column, by the Total value of the Check Amount column.

Those values in the chart are dynamic, and really fields that I can grab into the Text Object.

Re: Calculation on Totals row values help please....

Anonymous — Wed, 29 Apr 2015 16:09:53 GMT

Rick,

It can be a text object with a text that starts with the "=" sign, for example, for average size of sales it would be

=sum(Sales)/count(Sales)

and, to format in a way you want, you can use a formatting function, e.g.:

=num(sum(Sales)/count(Sales),'#,##0.00')

In you case, I assume it will be

=<expression for Total value>/<expression for Invoice $ Weighting>

Hope this helps...

Re: Calculation on Totals row values help please....

Wed, 29 Apr 2015 16:27:25 GMT

Thanks so much...

After quite a bit of toying around with your suggestions as a base... ended up coming up with this... that succeeded in returning the correct value.

='Weighted DPO:'&NUM(SUM(((aggr(avg(num([Check Date])-num([Invoice Date])),[KEY_CompanyInvoiceNumberVendorNum]))*[Invoice $]))/(Sum([Check Amount])),'#,##0')

Re: Calculation on Totals row values help please....

Anonymous — Wed, 29 Apr 2015 16:33:54 GMT

Yep, you got the idea

One more hint - if you want to have the number on the new line, add chr(10) or chr(13). It will be

='Weighted DPO:'&chr(10)& NUM(SUM...