topic Re: Prorate sale amount in QlikView

Prorate sale amount

andonialbisu — Fri, 29 Jun 2018 11:31:59 GMT

Hello everybody,

I don't know if it's possible to do what I want but, here it is:

What I have is:

key	client	amount
1	A	1000
1	B	1000
1	C	2000
2	A	1000
2	D	500

What I want is:

key	client	amount	amoun_prorated
1	A	1000	amount1A + 0,5amount1B + 0,5amount1C = 2500
1	B	1000	0,5*amount1B = 500
1	C	2000	0,5*amount1C =1000
2	A	1000	amount2A + 0,5*amount2D = 1250
2	D	500	0,5*amount2D = 250

So, I explain myself:

amount_prorated for client A is the Sum of the 50% of each amount where it's key equals.
amount_prorated for another client is the 50% of his amount if exists client A for its same key.

I don't know if it's clear of not...

Thanks in advance.

Andoni

Re: Prorate sale amount

sunny_talwar — Fri, 29 Jun 2018 14:45:10 GMT

Try this

Table:

LOAD key,

client,

amount,

If(client = 'A', amount, amount/2) as temp_amount;

LOAD * INLINE [

key, client, amount

1, A, 1000

1, B, 1000

1, C, 2000

2, A, 1000

2, D, 500

];

Left Join (Table)

LOAD key,

'A' as client,

Sum(temp_amount) as NewAmount

Resident Table

Where client <> 'A'

Group By key;

FinalTable:

LOAD key,

client,

amount,

RangeSum(temp_amount, NewAmount) as amount_prorated

Resident Table;

DROP Table Table;

Re: Prorate sale amount

andonialbisu — Fri, 29 Jun 2018 15:01:44 GMT

Hi Sunny,

I think I forgot something. If there's no A client mixed with others with the same Key, amount_prorated should be 100% of each client. I give an example modifying the data used above (new data in bold):

key	client	amount
1	A	1000
1	B	1000
1	C	2000
2	A	1000
2	D	500
3	B	500
3	F	1000

key	client	amount	amoun_prorated
1	A	1000	amount1A + 0,5amount1B + 0,5amount1C = 2500
1	B	1000	0,5*amount1B = 500
1	C	2000	0,5*amount1C =1000
2	A	1000	amount2A + 0,5*amount2D = 1250
2	D	500	0,5*amount2D = 250
3	B	500	= amount3B = 500
3	F	1000	= amount3F = 1000

Sorry for the inconvenience and thanks for your answer.

Andoni

Re: Prorate sale amount

sunny_talwar — Fri, 29 Jun 2018 15:11:20 GMT

Try this

Table:

LOAD key,

client,

amount;

LOAD * INLINE [

key, client, amount

1, A, 1000

1, B, 1000

1, C, 2000

2, A, 1000

2, D, 500

3, B, 500

3, F, 1000

];

Left Join (Table)

LOAD key,

Sum(amount/2) as NewAmount1

Resident Table

Where client <> 'A'

Group By key;

Left Join (Table)

LOAD key,

Sum(amount/2) as NewAmount2

Resident Table

Where client = 'A'

Group By key;

FinalTable:

LOAD key,

client,

amount,

If(client = 'A', RangeSum(amount, NewAmount1), If(IsNull(NewAmount2), amount, amount/2)) as amount_prorated

Resident Table;

DROP Table Table;