Calculated Date Dimension

[paul_scotchford]

Hi All

I have created a calculated dimension using the below expression (1).

The variables contain a date e.g. a Start date and a End Date

I was hoping the dimension would be a resultant set of dates but I get none.

If I hard code the from / to dates instead of the variables , it works fine (yes the variables have values )

Oddly enough the second (2) expression returns a set of dates.

It seems that the <= is not evaluating when a variable is used.

Has anyone tried something similar ? Am I coding this incorrectly ?

1: if(CalendarDate > $(s6MthPrevEom) and CalendarDate  <= $(sPrevMthEom) ,CalendarDate)

2: if(CalendarDate > $(s6MthPrevEom) ,CalendarDate)

Thanks in advance

[tresesco]

Could you post your sample qvw?

[paul_scotchford]

No sorry too big and secure

[tresesco]

You can create a sample or see: Preparing examples for Upload - Reduction and D... | Qlik Community

[PradeepReddy]

Remove the dollar symbol before the variable as bellow and try it...

1: if(CalendarDate > s6MthPrevEom and CalendarDate  <= sPrevMthEom ,CalendarDate)

2: if(CalendarDate > s6MthPrevEom ,CalendarDate)

[SunilChauhan]

we have also faced this but get resolved problem using below

you can define varible inscript as follows

let var="=(Your expression)";

and in your expression  use var not $(var)

[paul_scotchford]

I tried those methods prior to posting the issue here.

Many thanks for your suggestion.

[jonathandienst]

Hi

If expression 2 works properly, then the problem is related to sPrevMthEom. What do the variables actually contain? You can see this by using the Settings | Variable Overview. I suspect that s6MthPrevEom is a number (so Expr2 works) and sPrevMthEom is a date string, so it would need to be in quotes.I would change the value of sPrevMthEom to a value as well.

HTH

Jonathan

[#9]

Can you elaborate your variables.. It will helps to find the solution

[paul_scotchford]

Hi , thanks for the input, the two variables expand out as shown below, the fTxn is used to get the max value of the sales transactions for the country of interest (as we work across time zones)) 

Both vars return dates of format 'yyyy-mm-dd', we use these vars elsewhere in apps but not as conditional dimensions, hence they do work for other areas of the apps we have. Oddly though, these worked at one point during debugging when I removed the $() from them in the dimension calc.

The only reason I use a calc dimension is that set analysis did not work for a very large formula to work out a risk score , I'm going to try to

pre-calc a score in the load script so I can use set analysis in this app.

sPrevMthEom = date(AddMonths(monthend(max(if(fTxn=1,CalendarDate))),-1))

s6MthPrevEom = date(MonthEnd(addmonths(sPrevMthEom,-6)))