Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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????
Hi,
=if(date(vDate) - date([Due Date]) <=10,sum([Balance in Trans Currency]))
Check both are in same format too..
Try like this.
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?
Check this
sum({<[Due Date]={">=$(=date(vDate + 10,'MM/DD/YYYY'))<=$(=date(vDate))"}>}[Balance in Trans Currency])
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]))
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..
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])
I tried that & it didn't seem to work. When I put a date in the input box, it doesn't change anything.
Have you set the variable number format as date?
I have the variable set up like this at load time:'
LET vDate = (Today());