topic Using Date Variables in Set Analysis in QlikView

Using Date Variables in Set Analysis

Fri, 02 Sep 2011 15:57:40 GMT

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?

Using Date Variables in Set Analysis

lucas4bi — Fri, 02 Sep 2011 16:05:55 GMT

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)

Using Date Variables in Set Analysis

Fri, 02 Sep 2011 17:55:50 GMT

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.

Using Date Variables in Set Analysis

lucas4bi — Mon, 05 Sep 2011 07:40:21 GMT

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

Using Date Variables in Set Analysis

Tue, 06 Sep 2011 15:59:36 GMT

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?

Using Date Variables in Set Analysis

lucas4bi — Wed, 07 Sep 2011 09:32:08 GMT

you write the variable like this: $(CurrentDate)

try with that

Using Date Variables in Set Analysis

Wed, 07 Sep 2011 19:07:30 GMT

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

Using Date Variables in Set Analysis

lucas4bi — Thu, 08 Sep 2011 16:18:01 GMT

Give a look at the function monthend()

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

Using Date Variables in Set Analysis

Thu, 08 Sep 2011 18:07:22 GMT

I have been able to create another variable based on either:

=Date(MonthEnd('$CurrentDate)',-1,'MM/DD/YYYY')

=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.

Using Date Variables in Set Analysis

Thu, 08 Sep 2011 18:36:05 GMT

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

Using Date Variables in Set Analysis

Fri, 30 Sep 2011 15:00:18 GMT

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!

Using Date Variables in Set Analysis

GabrielAraya — Fri, 30 Sep 2011 15:02:39 GMT

Try
={">=$(=Today())"} >}

Gabriel

Using Date Variables in Set Analysis

Fri, 30 Sep 2011 15:26:25 GMT

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?