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?
That's because you dont have to set that variable in a script, but you should just set a document variable.
If you go on "Settings" you will see "Variable Overview", there you can set a document variable, which you can use in your set analisys expression.
Let me now
Hi
I have 2 suggestions:
1) if you wanna use a variable, try setting document varibla, not loading a field in the script. like this:
CurrentDate = max({1} Date)
then you can try this in your expression:
sum({<Date=$(CurrentDate), Forecast={'Actual'}>}AvgBalance)
2)if you wanna use the "monthend" way, you can try using the floor() function, this should completly eliminate the time from the date:
=sum({Date={floor((MonthEnd(Today(),-1))}, Forecast={'Actual'}>}AvgBalance)
Thanks for the response, but I couldn't get either suggestion to work.
I copied in CurrentDate = max({1} Date) in to the qlikview load statement and it errored out on reload.
I also tried the floor statement, but the expression builder has an issue with the { before floor.
I may be forgetting something, I am still very new to this.
That's because you dont have to set that variable in a script, but you should just set a document variable.
If you go on "Settings" you will see "Variable Overview", there you can set a document variable, which you can use in your set analisys expression.
Let me now
I was able to create an expression that returns the correct date I am looking for:
=max({<Forecast={'Actual'}>}Date)
I created a document variable CurrentDate and defined it with the above expression.
But when I try to add the variable in the set analysis expression
=sum({<Date={CurrentDate}, Forecast={'Actual'}>}AvgBalance)
It does not work. Am I plugging CurrentDate in correctly?
you write the variable like this: $(CurrentDate)
try with that
Worked. Thanks, I appreciate all of the help.
Any idea on how I can now create another variable for the previous month.
So if =max({<Forecast={'Actual'}>}Date) returns 07/31/2011
I need to now get 06/30/2011
Give a look at the function monthend()
that can help you doing what you need setting the right parameters
I have been able to create another variable based on either:
=Date(MonthEnd('$CurrentDate)',-1,'MM/DD/YYYY')
or
=Date(AddMonths('$CurrentDate)',-1)'MM/DD/YYYY')
both of which give a value of 6/30/2011
But if I use this variable in a set analysis express as I did with the first variable I don't get a value
so =sum({<Date={'$(PreviousDate)'),Forecast={'Actual'}>}AvgBalance gives me a value of 0
but =sum({<Date={'$(CurrentDate)'},Forecast={'Actual'}>}AvgBalance gives me a real value
I thought that maybe using a variable inside another variable could cause problems, so I replaced the '$CurrentDate' in the variable definition with CurrentDate's equation. That still returned the proper value for the PreviousDate variable, but once used in an expression it returns the same 0 value.
Figured it out, looks like it didn't like the MM/DD/YYYY format, had to be M/DD/YYYY. Thanks.