Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
febridwi
Partner - Contributor III
Partner - Contributor III

Calculate amount on different date range

Hi Everyone,

I have a case about calculation based on date valid range. As you can see below, There are Transaction and Validation Table, then I try to get sum(QTY*HARGA) based on date valid range of PRODUCT A, in the final result of sum(QTY*HARGA)  must be 540000. But after I did the calculation the result is 1100000. I was confused why the result is not correct, so I make a new table to check in detail, and I saw there are some TGL TRANSAKSI include even not in date valid range (GET_DateFrom). 

Table Check.PNG

 

 

 

 

 

 

My question is how take out the TGL TRANSAKSI  which is "must not include"?  Does anyone have any advice? 
I attached the Qvw & data also. Hopefully, it can help.

Have a nice day, Febri.

Labels (3)
1 Solution

Accepted Solutions
sunny_talwar

You can try this also

Sum(If([TGL TRANSAKSI] >= DateFrom and [TGL TRANSAKSI] < DateTo, QTY*HARGA))

View solution in original post

5 Replies
jyothish8807
Master II
Master II

Hi,

Try this calculated dimension and expression:

Dimension:=if(len(If([TGL TRANSAKSI] >= DateFrom and [TGL TRANSAKSI] < DateTo , DateFrom))>0,PRODUCT)

Exp:sum( {<[TGL TRANSAKSI]= {">=$(DateFrom) <$(DateTo)"}>} QTY* HARGA)

Best Regards,
KC
anushree1
Specialist II
Specialist II

please check the attached

sunny_talwar

You can try this also

Sum(If([TGL TRANSAKSI] >= DateFrom and [TGL TRANSAKSI] < DateTo, QTY*HARGA))
febridwi
Partner - Contributor III
Partner - Contributor III
Author

@anushree1 Thank you for the advice. unfortunately, when I calculate SUM(QTY) the result is not correct 😕 

I guess because you use outer join. It corrects to get SUM(QTY*HARGA) but not for SUM(QTY)

febridwi
Partner - Contributor III
Partner - Contributor III
Author

Great @sunny_talwar It works. Thanks a lot