Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a set analysis expression with two variable, Date, and Forecast.
=sum({<Date={'7/31/2011'}, Forecast={'Actual'}>}AvgBalance)
I am trying to use a more flexible expression for the Date variable. Essentiall I want the date to be the most recent with an Actual value.
I have tried using a sql select statement to create a new variable named CurrentDate in the load:
sql select MAX(date) as CurrentDate from graph_data where Forecast = 'actual';
This works fine and the value of CurrentDate is correct. But when I try to add this new variable to the set analysis, it doesn't work:
=sum({<Date=CurrentDate, Forecast={'Actual'}>}AvgBalance
Is this possible?
I have also tried to get a date based off of today() function. Using Date(MonthEnd(Today(), -1)'MM/DD/YYYY') I can a date I can live with.
However if I substitute this expression into the set analysis for '7/31/2011' it will not work.
=sum({Date={'(MonthEnd(Today(),-1)'MM/DD/YYYY')}, Forecast={'Actual'}>}AvgBalance
In reading past posts this may not be working due to MonthEnd() adding time onto the value.
If I try a if(CurrentDate = Date, 'True', 'False') or substitute the expression above for CurrentDate I do get a True value for Date 7/31/2011, so they do equal in this expression.
Any Advice?
I can't seem to get the Today function to work inside a Set statement.
I have the following statement, which works fine if I hard code an actual date in it, but I want to show a rolling 10 weeks of data in my chart.
=avg({$<[MI Date]={'>=today()-70'}>}[DS_TotalScore])
If anyone can tell me where I'm going wrong, I would greatly appreciate it!
Try
={">=$(=Today())"} >}
Gabriel
Thanks Gabriel...I can get there to work, but it doesn't work when I put in -70 in the forumla to look back 70 days.
Here's what I have now:
=avg({$<[MI Date]={">=$(=Today()-70)"} >}[DS_TotalScore])
Do you see anything wrong?