15 Replies Latest reply: Jul 27, 2012 5:30 PM by Michelle Malone

# Subtraction of two dates to get aging problem

I seem to be having a problem with date fields.  I'm trying to set up an AR aging summary and my data is not showing up in the expressions I have set up.

I have an input field so the user can enter an 'AS of Date'

The variable for that is called vDate.

In my initial load I have the vDate defaulting to the system date.

LET vDate = (Today());

I have tried 2 different variations

In my table, I have the following expressions but they don't seem to be working:

=if(date(\$(vDate)) - date([Due Date]) <=10,sum([Balance in Trans Currency]))

=if(num(\$(vDate)) - num([Due Date])>=11 AND Num(\$(vDate)) - Num([Due Date])<=30,sum([Balance in Trans Currency]))

Any ideas on what my problem might be????

• ###### Re: Subtraction of two dates to get aging problem

Hi,

=if(date(vDate) - date([Due Date]) <=10,sum([Balance in Trans Currency]))

Check both are in same format too..

Try like this.

• ###### Re: Subtraction of two dates to get aging problem

That didn't seem to help it.  When I input a date, it is not returning anything.

The due date is in mm/dd/yyyy format.

Would that make a difference?

• ###### Re: Subtraction of two dates to get aging problem

Check this

sum({<[Due Date]={">=\$(=date(vDate + 10,'MM/DD/YYYY'))<=\$(=date(vDate))"}>}[Balance in Trans Currency])

• ###### Re: Subtraction of two dates to get aging problem

How would this work for when I'm comparing between 11 days and 30 days?

=if(num(\$(vDate)) - num([Due Date])>=11 AND Num(\$(vDate)) - Num([Due Date])<=30,sum([Balance in Trans Currency]))

• ###### Re: Subtraction of two dates to get aging problem

HI

Try like this, Its separate expression

sum({<[Due Date]={">=\$(=date(vDate + 30,'MM/DD/YYYY'))<=\$(=date(vDate)+11)"}>}[Balance in Trans Currency])

Edit: Can you post a file?

Hope it helps..

• ###### Re: Subtraction of two dates to get aging problem

On comparing set analysis and if conditions set analysis works faster than if conditions.

better use set analysis for this purpose.

Have you checked by in your conditions

=if((num(\$(vDate)) - num([Due Date]))>=11 AND (Num(\$(vDate)) - Num([Due Date]))<=30,sum([Balance in Trans Currency]))

Change this to set analysis as below

sum({<[Due Date]={">=\$(=date(vDate + 30,'MM/DD/YYYY'))<=\$(=date(vDate + 11))"}>}[Balance in Trans Currency])

• ###### Re: Subtraction of two dates to get aging problem

I tried that & it didn't seem to work.  When I put a date in the input box, it doesn't change anything.

• ###### Re: Subtraction of two dates to get aging problem

Have you set the variable number format as date?

• ###### Re: Subtraction of two dates to get aging problem

I have the variable set up like this at load time:'

LET vDate = (Today());

• ###### Re: Subtraction of two dates to get aging problem

Go to Settings-->Document settings-->Number tab-->Change the drop down to variables

then select the vDate variable then change the format.

• ###### Re: Subtraction of two dates to get aging problem

I made the change but still nothing.

• ###### Re: Subtraction of two dates to get aging problem

i can but that will take me a little while; have to leave out shortly.  i will post when i return.

• ###### Re: Subtraction of two dates to get aging problem

I have now attached the sample data and screenshots of what I have in qlikview.

• ###### Re: Subtraction of two dates to get aging problem

Hi

There is no data for last 30 days from today

sum({<[Due Date]={">=\$(=date(vDate + 60,'MM/DD/YYYY'))<=\$(=date(vDate + 11))"}>}[Balance in Trans Currency])

Check this, You can find the result.

• ###### Re: Subtraction of two dates to get aging problem

There should be several buckets since this is aging:

1 - 10 days

11 - 30 days

31 - 45 days

46 - 60 days

over 61 days

so all my data should fall under the over 61 days column but it is not showing anything.