topic Using an expression as dimension in QlikView

Using an expression as dimension

Thu, 02 Oct 2014 15:48:59 GMT

I'm trying to make several rolling time period charts of varying length for a dashboard.

I'm using the following expression for my dimension (field is "LDate" which is a date from my linked calendar):

=IF((Num(LDate)) >= Num(Today()-7), LDate)

What I expected to see was a chart with 7 days listed. However here's what displays:

Why is "today" not showing up as a date but as a - mark and showing my entire data count instead of for today only?

Colin-Albert — Thu, 02 Oct 2014 15:56:36 GMT

You need to put the filter in your expression not the dimension.

Use LDate as your dimension

Created Expression = count( {<DateCreated={">$(=today()-7)<=$(=today())"}>} YourDataID )

ResolvedExpression = count( {<DateResolved={">$(=today()-7)<=$(=today())"}>} YourDataID )

Obviously you will need to change DateCreated, DateResolved and YourDataID to match your field names

Colin-Albert — Thu, 02 Oct 2014 15:59:18 GMT

You should set your dimension to "Supress when value is null"

Your expression will set LDate as null for every date apart from the past 7 days. This is the total you are seeing in the ( - ) column.

Thu, 02 Oct 2014 16:15:06 GMT

It worked perfectly. I'm assuming if I wanted prior X months I'd rewrite as below:

Created Expression

= count( {<Month(DateCreated)={">$(=Month(today())-X)<=Month($(=today()))"}>} YourDataID )

Colin-Albert — Thu, 02 Oct 2014 16:26:43 GMT

Set Analysis works on the field names not and expression, so for prior X months you can use Addmonths()

Created Expression

= count( {<DateCreated={">$(=Addmonths(today(), -X) )<=($(=today()) "}>} YourDataID )

You can also use monthstart() or monthend() to get the start or end of the month

Thu, 02 Oct 2014 21:03:57 GMT

Ok, one final stupid question. I'm trying to get my dynamic dimension to do X number of months.

I've tried:

=If (Num(Month(LDate)) >= Num(Month(Today())) - 3, MonthYear)

And several variations but its always showing my entire data set. What am I doing wrong?