Get the Previous Year's Only for Days Between two dates(Relational)

[HelloCognos]

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

[MFGF]

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.

[Francis aka khayman]

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.

[HelloCognos]

WHOMM! Interesting. OK, I'll try that. thanks

[HelloCognos]

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

[Francis aka khayman]

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

[HelloCognos]

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

[Francis aka khayman]

awesome!