topic Re: Expression Vs Variable Expression in QlikView

Expression Vs Variable Expression

Fri, 27 Mar 2015 08:38:01 GMT

Dear Team,

i am using a Expression in a TEXT object in 2 different way and getting 2 different out but

my Max MonthYear is : Feb-2015.

1. ) =date(addmonths(max(MONTHYEAR),-3), 'MMM-yyyy') OUTPUT : Nov-2014 ( Correct )

2. ) =date( $(=AddMonths(max(MONTHYEAR),-3)),'MMM-yyyy') OUTPUT : Dec-1899 ( Wrong)

if i am removing Date() function then its show numeric value as below :

3) =$(=AddMonths(max(MONTHYEAR),-3)) OUTPUT : 0.0054617676266137

Kindly let me know what is the issue.

Actually i want to get last 3 Month sum of Turnover so i need to use this into the set analysis.

Thanks & Regards,

JKV

Re: Expression Vs Variable Expression

prieper — Fri, 27 Mar 2015 09:04:50 GMT

Would be good to post an example and to see your data-formats.

It may also help to copy your formulas into a straight table and leave the expression label empty. On mouse-over you then can see the individual values used for the calculation.

Peter

Re: Expression Vs Variable Expression

Fri, 27 Mar 2015 09:13:59 GMT

below is what i am getting.. label is showing right information but the expression showing wrong

MONTHYEAR	11/1/2014
	12/30/1899
Feb-2015	12/30/1899

Re: Expression Vs Variable Expression

Fri, 27 Mar 2015 09:15:34 GMT

use this instead and you will get the same output as =date(addmonths(max(MONTHYEAR),-3), 'MMM-yyyy')

= date($(=new),'MMM-YYYY')

new is a variable with value- AddMonths(date(max(Date)),-3)

Re: Expression Vs Variable Expression

Fri, 27 Mar 2015 09:19:29 GMT

yes i know if i use into a variable i get the information but why for such small expression i use variable.

even lots of example in qlikview community show that you can direct use such expression in set analysis.

Re: Expression Vs Variable Expression

Colin-Albert — Fri, 27 Mar 2015 09:19:51 GMT

The $(= ) applied to the date is calculating the values in the date as numbers divided by the various date parts.

So $(= 27/3/15) gives 27/3 = 9 then 9/15 = 0.6 !

$(= 27/3/2015) gives 27/3 = 9 then 9/2015 = 0.00446650124 !

Re: Expression Vs Variable Expression

Fri, 27 Mar 2015 09:25:23 GMT

Dear Colin,

But why the below expression showing two different result.

In the Label is showing the right information, but in Expression it showing the wrong.

=date( $(=AddMonths(max(MONTHYEAR),-3)),'MMM-yyyy')

MONTHYEAR	11/1/2014
	12/30/1899
Feb-2015	12/30/1899

Expression Vs Variable Expression

Re: Expression Vs Variable Expression

Anonymous — Fri, 27 Mar 2015 09:27:38 GMT

with your above code you will get last 2 months and plus current MTD

to get last 3 months you will have to select data where Date <=Date(=monthstart(today())-1))

AND Date >=Date(=MonthStart(MonthStart(Today())-85)

And if you can upload sample qvw that would be great to help !