Sum aggr with double counting

[awalhashim]

Hi all,

I need to calculate the selling price of a products with this set of double counting data

saleid, interestid, saledate, interestdate, status, interestbu, systembu, source, and price.

The selling process as below:

1. Book -> Return

or

2. Book -> Sign

or

3. Book -> Sign -> Return

They cannot skip these process.

there are also some conditions to fulfil.

1. interestbu and systembu must be from the same bu

2. Interestdate < saledate

3. saledate - interestdate < 0 and > 365

so i came up with the code.

If(status = 'Sign',
	sum({<source=>}
	if(systembu = interestbu, 
	if(interestdate < saledate, 
	if(saledate-interestdate > 0 
	and saledate-interestdate < 365, 
	Aggr(Only({<saleid = e({1<status = {'Return'}>})>}price),saleid))))),
If(status = 'Book' or status = 'Return',
	sum(
	if(systembu = interestbu,
	if(interestdate < saledate, 
	if(saledate-interestdate > 0 
	and saledate-interestdate < 365, 
	Aggr(price,saleid))))))) 

Apparently, the problem i faced is whenever "Book" process doesnt fullfil the  conditions and dont appear when i select "Book", the id appear when i select "Sign" which is skip the selling process. I will attached sample qvw.

I have select the id "87311" as example. U can toggle between Status "Book" and "Sign" to see the problem.

Thanks in advance

[sunny_talwar · Accepted solution]

For saleid 87311, interestbu is null for the row where interestdate < saledate which is why nothing is getting shown for status = Book

Could this be a data related issue?