Sum aggr with double counting

awalhashim — Sat, 16 Nov 2024 21:31:25 GMT

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

2. Book -> Sign

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

Re: Sum aggr with double counting

sunny_talwar — Mon, 28 Jan 2019 12:57:33 GMT

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?

topic Sum aggr with double counting in QlikView

Sum aggr with double counting

Re: Sum aggr with double counting