Subtraction of two dates to get aging problem

[#1]

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????

[MayilVahanan]

Hi,

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

     Check both are in same format too..

Try like this.

[#3]

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?

[CELAMBARASAN]

Check this

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

[#5]

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]))

[MayilVahanan]

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

[CELAMBARASAN]

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])

[#8]

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

[CELAMBARASAN]

Have you set the variable number format as date?

[#10]

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

LET vDate = (Today());