Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

15 Replies
MayilVahanan

Hi,

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

     Check both are in same format too..

Try like this.

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
Not applicable
Author

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
Partner - Champion
Partner - Champion

Check this

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

Not applicable
Author

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

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
CELAMBARASAN
Partner - Champion
Partner - Champion

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

Not applicable
Author

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

CELAMBARASAN
Partner - Champion
Partner - Champion

Have you set the variable number format as date?

Not applicable
Author

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

LET vDate = (Today());