Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
awalhashim
New Contributor II

Sum aggr with double counting

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

Labels (2)
1 Solution

Accepted Solutions

Re: Sum aggr with double counting

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

image.png

Could this be a data related issue?

1 Reply

Re: Sum aggr with double counting

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

image.png

Could this be a data related issue?