Date Function in Expression

[#1]

Hi All,

i need help in getting sun function flexible. currently i have 3 columns:

ID           Date                    Qty

1               28/3/2014          100

1               27/3/2014          1000

1               24/3/2014          2000

in expression i use below and i get sum value for that date:

Sum({Date = {'$(=Tdate)'}>}Qty gives be 100 ........ Assume variable Tdate = 28/3/2014

Now i want in same expression and get value of 1100 from Date 28/3/2014 & 27/3/2014

please advise what needs to be changed

thanks

[Yousef_Amarneh]

Try this

Sum({Date = {'$(=Tdate)','$(=Tdate-1)'}>}Qty)

[#3]

Thanks Yousef...

But the above was a sample data.... Date column have 1 year data.. thus your suggestion may not work..... i want to use

sum({Date > {'$(=Tdate)'}>}Qty) but it is not working ...guess the syntax is wrong

please advise

thanks

[Yousef_Amarneh]

Could you please give me more details, I cannot get you very well. what do you mean by flexible function?

[its_anandrjs]

Check your date format for field Date, it is in date format or in string.

Hope this helps

Thanks & Regards

[#6]

ok so i have 1 year data in this table and in expression i want to sum Qty where Date is greater than or equal to 1 week.

1 Week is a variable and i can display this variable in input box and ask user to change it as per its convenience

@ Anand - i checked the format of the variable and Date column is same... the Expression is i feel is incorrect

please advise

[Yousef_Amarneh]

Create 2 variables, vFromDate and vToDate then use the following expression

sum({<Date= {"<=$(=vFromDate)>=$(=vToDate)"}> } Qty)

[#8]

thanks Yousef, but the desired result is not coming

i defined the below variables:

vToDate - today's value

vFromDate - 24/03/2014

the table gives me sum of Qty for whole 1 year

please advise

[#9 · Accepted solution]

Try this:

Sum({Date = {">=$(=Tdate)"}>}Qty)

[MayilVahanan]

Hi

Try like this

vStartDate = 22/3/2014

vEndDate = 27/3/2014

=Sum({<Date = {">= $(=vStartDate) <= $(=vEndDate)"}>} Qty)

i checked the format of the variable and Date column is same...

So there is no need to apply date format, its will give correct answer.