Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have this in my data:
A column with dates like:
Date_Clean: 2013-09-01
I have these variables in my edit script:
LET vDateToday = num(Date(Today(), 'YYYY-MM-DD'));
LET vYesterday = num(date(vDateToday-1, 'YYYY-MM-DD'));
LET vPast15Days = num(date(vDateToday-15, 'YYYY-MM-DD'));
And this variable in my document:
vLast15Days ='>=$(vPast15Days) <=$(vDateToday)
I want to show sum(amount) on dates - my problem is:
This one works fine:
=sum({<Date_Clean={'$(vLast15Days)'}>} TotalPrice)
but this one doesnt not work:
=sum({<Date_Clean={'$(vYesterday)'}>} TotalPrice)
my output for the 3 variables are:
vDateToday= 41519
vYesterday = 41518
vLast15Days = >=41504 <= 41519
So why does my Yesterday variable not show any sum ? When it is included in vLast15Days..
Right, this should eliminate the need to care for a special format.
Probably because the field selection modifier expects a different date format.
If you Date_Clean is formatted like 'YYYY-MM-DD', try
=sum({<Date_Clean={'$(=Date(vYesterday,'YYYY-MM-DD))'}>} TotalPrice)
Thats not working And why should it suddenly except a differnt format? when i made my variable with that format?
LET vYesterday = num(date(vDateToday-1, 'YYYY-MM-DD'));
And vLast15Days is working fine? based on the same
Because QV may evaluate a search expression like ">StartDate<EndDate" differently compared to a plain value.
Try inputting the value without a variable or dollar sign expansion. Try also to select the date value in a list box only using the plain sum(TotalPrice).
I assume you have TotalPrice values for that date (maybe not - it was a sunday?).
I have amount on that date (so if i select the date with just sum(totalprice) i can see the amount.
Ill try the other thing,
and then i will try to load date_Clean as num(Date_clean) <- That should be best practice right?
Right, this should eliminate the need to care for a special format.
It worked to convert Date_clean to numeric dates.