Hi,
I'm back, :D :D
Do you know a way to do the following?
get the # of days between two dates like an Starting Date and Ending Date but for a specific year?
something like,
case,
when Year='2017'
then
_days_between (Ending_Date,Starting_Date)
else
(0)
Of course the above doesn't work as I'm getting zero.
Need to add _add_days to this I think like (_add_days(Ending_Date-365)) to go back one year?
Thanks in advance for your time.
:) :)
Quote from: HelloCognos on 29 Aug 2018 11:38:58 AM
Hi,
I'm back, :D :D
Do you know a way to do the following?
get the # of days between two dates like an Starting Date and Ending Date but for a specific year?
something like,
case,
when Year='2017'
then
_days_between (Ending_Date,Starting_Date)
else
(0)
Of course the above doesn't work as I'm getting zero.
Need to add _add_days to this I think like (_add_days(Ending_Date-365)) to go back one year?
Thanks in advance for your time.
:) :)
Hi,
I just tried a similar exercise in the GO Sales (query) sample package, and it works fine for me. The expression I used was:
if ([Year] = 2010) then (_days_between ([Date (close date)], [Date])) else (0)
In your case, is the Year item a character string or a numeric value? Are Ending_Date and Starting_Date date items?
Cheers!
MF.
if you are getting zero, it means that the result of Year = '2017' is false.
to confirm, try to change else (0) to something like else (9999) and test again. then you know where to check exactly.
WHOMM! Interesting. OK, I'll try that. thanks
Ok, it is returning 9999 so you right, the logic is not working.The Data Item is dropped on top of a Singleton.When I actually run the View Tabular on the Query,I see the 2017 related data which are non zero. Also, when I put the 2017 comparison in the Query's Detailed Filter so only 2017 is returned, I get the
data. I'm pretty sure there is something that the Singleton is not liking...
Let me know what you think?
Thanks
singletons display single data (hence the name). meaning if your query returns multiple rows, singleton will display the data in the first row.
i am not sure though if that will help solve your issue or there is something else i am missing
OK, Thanks for your time.
I think since the Singleton requires one single value, I have to aggregate the data to a single return based on the conditions specified just like when I add the filter to the Queries detailed filter, except, I have to do it at Data Item level.
:)
awesome!