Using an expression as dimension

[#1]

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 · Accepted solution]

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]

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.

[#4]

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]

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

[#6]

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?