Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Partner
Partner

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.

1 Solution

Accepted Solutions

Re: Calculate amount on different date range

You can try this also

Sum(If([TGL TRANSAKSI] >= DateFrom and [TGL TRANSAKSI] < DateTo, QTY*HARGA))
5 Replies
Highlighted
jyothish8807
Honored Contributor II

Re: Calculate amount on different date range

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
Valued Contributor II

Re: Calculate amount on different date range

please check the attached

Re: Calculate amount on different date range

You can try this also

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

Re: Calculate amount on different date range

@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)

Partner
Partner

Re: Calculate amount on different date range

Great @sunny_talwar It works. Thanks a lot