Using Date Variables in Set Analysis

[#1]

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?

[lucas4bi]

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)

[#3]

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. 

[lucas4bi · Accepted solution]

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

[#5]

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?

[lucas4bi]

you write the variable like this:  $(CurrentDate)

try with that

[#7]

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

[lucas4bi]

Give a look at the function monthend()

that can help you doing what you need setting the right parameters

[#9]

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.

[#10]

Figured it out, looks like it didn't like the MM/DD/YYYY format, had to be M/DD/YYYY.  Thanks.