Why these two expressions act differently?

[#1]

Greetings all,

I have two expressions to calculate MTD sales:

for example these values will be selected by the user : Year=2014, Month=Jun, Day=22

Exp1:

sum({<Year={$(vL.CurrentYear)},Month={$(vL.CurrentMonth)},CreatedDate={'<=$(vL.CurrentDate))'}>}Price)

Exp2:

sum({<Year={$(vL.CurrentYear)},Month={$(vL.CurrentMonth)},Day={'<=$(vL.CurrentDay)'}>}Price)

CreatedDate is my link date field between Sales fact table and my master calendar.

while Exp2 gives the expected result,  Exp1 only bring the values of the 22nd of June whereas it should cover all the days of the month because of the "<=" sign in the expression.

could anyone please points out to me what I am doing wrong in Exp1?

[#2]

Hi Ali ,

According to you CreatedDate field link between MasterCal & Sales So,in this case if CreateDate format is not matching with your selection it will reflect all values instead of selection.

Just check your format whether is is in proper date format or not.

Regards,

Harshal

[michael_maeuser]

you have one ) too much in the first expression

sum({<Year={$(vL.CurrentYear)},Month={$(vL.CurrentMonth)},CreatedDate={'<=$(vL.CurrentDate))'}>}Price)

maybe that´s why

[#4]

No that was not it.

[#5]

the format is the same

[#6]

Hi Ali,

Try this,

sum({<Year={$(vL.CurrentYear)},Month={$(vL.CurrentMonth)},CreatedDate={'$(vL.CurrentDate)-1)'}>}Price)

[#7]

I think this only sets the CreatedDate to one day before.  it does not calculate MTD.