topic How to make a As/Of Date in QlikView

How to make a As/Of Date

rcandeo — Fri, 08 May 2009 03:31:08 GMT

I am devoloping an Acount Receivable application and my problem is with AS/OF Date.

My user needs a fied where he can put any as/of date and I need to show the payment informations as the as/of date is the actual date.

Because of that I cannot make the calculation using the due date and the actual date.

Anyone has developed something like this and can show me how to do?

Thank You

How to make a As/Of Date

johnw — Sat, 09 May 2009 01:15:10 GMT

It sounds like you want your "As of Date" to substitute for today() in your calculations. If so, I'd make an "As of Date" variable or field disconnected from the data model. I'd set it to today() with a macro that triggers OnOpen of the document, and if a field, set that only one value can be selected. Then I'd just use "As of Date" everywhere instead of today().

Or am I misunderstanding the problem?

How to make a As/Of Date

rcandeo — Sat, 09 May 2009 02:16:30 GMT

I appreciate your suggestion, but my problem is bigger than that. I´ve actually already used an as of date variable to show the informations. But I need to display some results that are calculated in my script.

I'll try to explain better.

A voucher has the due date as 04/05 and I am doing during the script a calculation that classifies all documents overdue as bellow:

1-15 days overdue

15-30 days overdue

>30 days overdue.

If I run the application today (05/08), the result for this specif voucher is > 30 days overdue.

But if my user puts in the as/of date variable 04/30, he expect to receive the result as 15-30 days overdue cause from 04/05 to 04/30 is only 20 days.

Am I being clear about my problem? I Need an idea for the best way to solve this.

How to make a As/Of Date

Sat, 09 May 2009 02:55:27 GMT

Maybe you can try using the inputfield statement for the field you want to change

Inputfield asofdate;

then your load statement.

Hope this helps

Talha

How to make a As/Of Date

johnw — Sat, 09 May 2009 02:59:37 GMT

Well you won't be able to calculate that in the script anymore, since it is something that the user can modify. You'll need to calculate the overdue information and classification on the fly the same way you currently do in the script. And if you have a list box for the overdue range, you'd need to replace it with a list box for an expression that calculates the overdue range.

The expression would be something like this:

if("As of Date"-"Due Date"<= 0, 'not overdue'

,if("As of Date"-"Due Date" <=15, '1 to 15 days overdue'

,if("As of Date"-"Due Date" <= 30, '15 to 30 days overdue','>30 days overdue')))

If you end up using it in a lot of spots, you could make it a variable so that it's easy to plug in. If you need it as a dimension, you can use a calculated dimension, and so on.