topic Re: Why these two expressions act differently? in QlikView

Why these two expressions act differently?

Wed, 16 Jul 2014 11:16:16 GMT

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?

Re: Why these two expressions act differently?

Wed, 16 Jul 2014 11:50:50 GMT

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

Re: Re: Why these two expressions act differently?

michael_maeuser — Wed, 16 Jul 2014 12:07:57 GMT

you have one ) too much in the first expression

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

maybe that´s why

Re: Why these two expressions act differently?

Wed, 16 Jul 2014 12:12:33 GMT

No that was not it.

Re: Why these two expressions act differently?

Wed, 16 Jul 2014 12:30:49 GMT

the format is the same

Re: Why these two expressions act differently?

Wed, 16 Jul 2014 13:12:16 GMT

Hi Ali,

Try this,

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

Re: Why these two expressions act differently?

Thu, 17 Jul 2014 12:14:36 GMT

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