get the range sum

[#1]

COMPANY	ORDATE	dealdate	qty
A	2014/1/2	2013/12/20	30
		2014/1/10	10
		2014/2/1	20
B	2014/3/12	2014/1/5	40
		2014/3/6	20
		2014/4/4	30
		2014/4/12	40
		2014/5/1	50
C	2014/4/11	2014/2/5	20
		2014/3/6	30
		2014/4/4	40
		2014/4/12	50

the COMPANY &ORDATE is fixed data . When I input a date , I want to get the sum(qty)  between  ordate and  input-date for every company.

[#2]

the sample is as attached.

[robert_mika]

jack,

Are your data coming from Excel Pivot Table?

Do you have the raw sources of data?

[#4]

The data is from database .Attached is a actual data which ordate is property of company+item(which compose a key_gobkey).

[#5]

If you have Company as dimension, then expression:

sum( {<dealdate={"=INDATE<dealdate"}>*<dealdate={"<=$(=date(testdate))"}>} qty)

should work

regards

Darek

[#6]

it seem no ok . the expressing  dealdate={"=INDATE<dealdate"} don't work . pls see attached.

[#7 · Accepted solution]

This must work:

sum( {<dealdate={"<=$(=date(testdate))"}>} if(INDATE<dealdate,qty))

regards

Darek