Sum between dates - why is it behaving like this?

[Cathey]

I have managed to get sum between dates to work on user input variables:

sum({$<[Order Date]={">=$(vTYStartDate)<=$(vTYEndDate)"}>} [Order Sell])

where Order Date is my date field,

vTYStartDate & vTYEndDate are my user input start & end fields

Order Sell is the field I want to sum.

It is working fine EXCEPT that if vTYStartDate is greater than  vTYEndDate it seems to ignore the condition entirely rather than returning zero.

Why? What is the logic it is using to do this?

[y_grynechko]

Hey,

you can add if statement to the calc:

if((vTYStartDate)>$(vTYEndDate), 0,

sum({$<[Order Date]={">=$(vTYStartDate)<=$(vTYEndDate)"}>} [Order Sell]))

and see if it gives you what you need. 

[Cathey]

Thanks y_grynechko,

It works with :

if((vTYStartDate)>(vTYEndDate), 0,

sum({$<[Order Date]={">=$(vTYStartDate)<=$(vTYEndDate)"}>} [Order Sell]))

(needed to loose the $ in the first row)

My question, though is why do we have to do this - my expectation is that the rows shouldn't return in the first place?

If I can understand the logic behind why it behaves in this way then I'm more likely to write expressions that capture errors first time.

[y_grynechko]

I am really sorry but I am not sure why it works this way. I can only try to guess that it is on your side to figure out the correct range borders. So you should be sure to create a data model that doesn't allow the start date to be after the end date.