Set analysis greater than or equal to a date variable

[Dashman]

Having some trouble getting what should be a simple set analysis filter working.

I'm trying to calculate the average of a dimension over the past month. The past month date I have defined in the load script as a variable, vPast1Month. vPast1Month is this calculation: addmonths(today(),-1) and returns the date value exactly 1 month ago. What I currently have looks like this:

Avg( ${<DATE =   {">=$($vPast1Month)"}>} [Dimension])

This ends up returning everything and doesn't actually filter the date. Every other variation I've tried returns nothing. The reason why I'm using a variable is because I need to set this up with filterable button variables on '1 Month ago','3 Months ago', '6 Months ago', ect, which I intend to add as a series of if statements.

[sunny_talwar]

Try one of this

Avg({<DATE = {">=$(=vPast1Month)"}>} [Dimension])

or

Avg({<DATE = {">=$(=$(vPast1Month))"}>} [Dimension])

[Dashman]

Thanks for looking at this - tried both of these solutions, but unfortunately neither appear to produce any results. I've triple checked that my variable vPast1Month is correct  as $(=vPast1Month) will produce 5/5/2019 in a kpi tool alone. But when placed in the set analysis expression, no results. I've also tried manually entering the date and the equation instead, but still no results

[sunny_talwar]

What is your DATE field format?

[Dashman]

It's in date time format - eg. '4/2/2019 12:00:00 AM' ...

I've tried this as well but also doesn't work: Avg({<date(DATE) = {">=$(vPast1Month)"}>} [Dimension])

Edited to generic variable names*

[sunny_talwar · Accepted solution]

Assuming 4/2 means Apr 2nd.... Try this

Avg({<DATE = {">=$(=Date(vPast1Month, 'M/D/YYYY hh:mm:ss TT'))"}>} [Dimension])

or

Avg({<DATE = {">=$(=Date($(vPast1Month), 'M/D/YYYY hh:mm:ss TT'))"}>} [Dimension])

[Dashman]

Awesome, the bottom one works perfectly for me. Thank you!

[ShrutiV]

Hi, I am facing similar issue.

I tried both the solutions given but neither seem to be working for me. It gives zero value in KPI, but on filter selection it gives correct value. Below are the expressions I have tried

vAsonDateMTD : {">=$(=addmonths(Monthstart(Max(DATE)),-1))<=$(=addmonths(monthend(Max(DATE)),-1))"}

sum({<DATE = {">=$(=Date($(vAsonDateMTD), 'M/D/YYYY hh:mm:ss TT'))"}>} PRIMARY_VAL)/100000

sum({$<DATE={">=$(=addmonths(Monthstart(Max(DATE)),-1))<=$(=addmonths(monthend(Max(DATE)),-1))"}>}PRIMARY_VAL)/100000

I want the PRimary value for the past month, which should change on filter selection too.

Please help as I am unable to understand where am I going wrong.

Thanks