topic Re: Calculated Date Dimension in QlikView

Calculated Date Dimension

paul_scotchford — Fri, 02 May 2014 05:26:27 GMT

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

Re: Calculated Date Dimension

tresB — Fri, 02 May 2014 05:34:30 GMT

Could you post your sample qvw?

Re: Calculated Date Dimension

paul_scotchford — Fri, 02 May 2014 05:59:55 GMT

No sorry too big and secure

Re: Calculated Date Dimension

tresB — Fri, 02 May 2014 06:01:15 GMT

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

Re: Calculated Date Dimension

PradeepReddy — Fri, 02 May 2014 06:27:44 GMT

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)

Re: Calculated Date Dimension

SunilChauhan — Fri, 02 May 2014 06:32:59 GMT

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)

Re: Calculated Date Dimension

paul_scotchford — Sat, 03 May 2014 09:09:36 GMT

I tried those methods prior to posting the issue here.

Many thanks for your suggestion.

Re: Calculated Date Dimension

jonathandienst — Sat, 03 May 2014 09:34:14 GMT

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

Re: Calculated Date Dimension

Sat, 03 May 2014 11:08:08 GMT

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

Re: Calculated Date Dimension

paul_scotchford — Sun, 04 May 2014 22:03:07 GMT

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)))