topic Re: Expression needed to calculate an amount. in QlikView

Expression needed to calculate an amount.

Thu, 04 Jun 2015 15:35:24 GMT

Hello, everyone.

I have a table such as this.

Client #	Invoice #	Invoice Total ($)	Invoice Amount Paid ($)
1	10	100	40
1	10	100	50
1	11	200	100
1	11	200	30
1	12	300	150
2	13	900	250
2	13	900	220
2	14	800	460

And in QV, I have a Chart such as this, where I want to calculate the total pending amount:

Client	Total Pending Amount
1	?
2	?

That is, I want to take each Invoice Total for different Invoices and subtract Invoice Amount Paid.

For instance, for client 1, it would be: Total Pending Amount = 100 (because of invoice 10) + 200 (because of invoice 11) + 300 (because of invoice 12) - 40 - 50 - 100 - 30 - 150 (because of what has been paid for every invoice) = 230.

Could anyone help me please to achieve a expression for Total Pending Amount?

Thanks for reading.

Re: Expression needed to calculate an amount.

alexandros17 — Thu, 04 Jun 2015 15:41:07 GMT

Here it is:

Tab1:
Load * Inline [
Client #, Invoice #, Invoice Total ($), Invoice Amount Paid ($)
1, 10, 100, 40
1, 10, 100, 50
1, 11, 200, 100
1, 11, 200, 30
1, 12, 300, 150
2, 13, 900, 250
2, 13, 900, 220
2, 14, 800, 460
];

Tab2:
NoConcatenate
LOAD [Client #], Sum([Invoice Total ($)] - [Invoice Amount Paid ($)]) as InvPending Resident Tab1 Group By [Client #];
DROP Table Tab1;

Let me know

Re: Expression needed to calculate an amount.

Thu, 04 Jun 2015 15:49:27 GMT

Re: Expression needed to calculate an amount.

Thu, 04 Jun 2015 16:07:44 GMT

Alessandro, thanks for your answer.

Unfortunately, it's not what I'm looking for.

First, because I actually want a set analysis expression to do so, but more importantly, because you are repeating the invoice total for every invoice.

For instance, for invoice 10, my total is 100 - 40 - 50 = 10, and not 100 + 100 - 40 - 50. The invoice total for invoice 10 is 100, and not 200.

Thanks anyway for your answer.

Re: Expression needed to calculate an amount.

Thu, 04 Jun 2015 16:10:37 GMT

Hi Sandeep.trs -

As I told Alessandro, the main problem here is that I have several rows for the same invoice. In your attached example (which I appreciate) no invoices are repeated, so the sum is pretty straightforward.

If you can, look at my example, which has a valid scenario (with repeated invoices). As I told Alessandro:

"For instance, for invoice 10, my total is 100 - 40 - 50 = 10, and not 100 + 100 - 40 - 50. The invoice total for invoice 10 is 100, and not 200."

Thanks!

Re: Expression needed to calculate an amount.

Thu, 04 Jun 2015 16:23:18 GMT

Use this expression then

Sum(Aggr(sum(DISTINCT [Invoice Total ($)]),[Client #],[Invoice #]))-Sum(aggr(Sum([Invoice Amount Paid ($)]),[Invoice #],[Client #]))

Re: Expression needed to calculate an amount.

maxgro — Thu, 04 Jun 2015 16:28:48 GMT

sum(aggr(sum(DISTINCT [Invoice Total ($)]), [Client #], [Invoice #])) - sum([Invoice Amount Paid ($)])

Re: Expression needed to calculate an amount.

Thu, 04 Jun 2015 17:12:24 GMT

Hi,

Re: Expression needed to calculate an amount.

Digvijay_Singh — Thu, 04 Jun 2015 17:12:51 GMT

Same results by - Sum(DISTINCT [Invoice Total($)])-Sum([Invoice Amount Paid($)]) and Client# as the Dimension in Straight table chart

Client #	Invoice #	Invoice Total ($)	Invoice Amount Paid ($)
1	10	100	40
1	10	100	50
1	11	200	100
1	11	200	30
1	12	300	150
2	13	900	250
2	13	900	220
2	14	800	460

Client #	Invoice #	Invoice Total ($)	Invoice Amount Paid ($)
1	10	100	40
1	10	100	50
1	11	200	100
1	11	200	30
1	12	300	150
2	13	900	250
2	13	900	220
2	14	800	460

Client #	Invoice #	Invoice Total ($)	Invoice Amount Paid ($)
1	10	100	40
1	10	100	50
1	11	200	100
1	11	200	30
1	12	300	150
2	13	900	250
2	13	900	220
2	14	800	460