Can someone help me with this expression?

[#1]

Hello Experts,

I have 2 variables, HistoryDateFrom and HistoryDateTo, which store values from a Date field called Effective_Start. The problem is when I check the variable in a text object it returns as number instead of date and I know in the script I am converting this to date...

In a text box if I do =$(vHistoryDateFrom)    I Get 40675

But when I do = date($(vHistoryDateFrom))   I Get 15/11/2010

So to hardcode it I am writing a below expression which works on some other application and not here.

=count ({<Effective_start = {'<=date($(=vHistoryDateTo))'},Effective_end = {'>=date($(=vHistoryDateFrom))'}>}distinct (dim_opportunity.crm_opportunity_id & dim_opportunity.Status))

Can anyone help me to convert my variables in this expression to date format and then calculate??/

Thanks in advance

Mady

Anyone please??

[#2]

Hi,

what i understood from you that you want to count field values between start and end date and these dates are in variables

That's True??

[#3]

True, and i have given the situation clearly as well, what I have. Again I want to count something between the selected dates, which show as numbers and I think thats casuing the calculation problems... But when I wrap those variables with Date() they show the correct dates, now i want to use this similar function with Set Analysis expression.

Thanks Again

[deepakk]

hi,

You can work it in below way.

create a number field  for Date Effective_start and Effective_end and use it in your expression

count ({<Effective_start_num = {'<=($(=vHistoryDateTo))'},Effective_end_num = {'>=($(=vHistoryDateFrom))'}>}distinct (dim_opportunity.crm_opportunity_id & dim_opportunity.Status))

This will sort out your issue.

[#5]

Hello Deepak,

I am unsure what you meant by asking me to create a num field for variables?

Now I have my variables as Calendar Objects, where the end user can select dates. What do you want me to try?

Can you please explain a bit in detail.

Thank again for your time.

Mady

[#6]

Simple:

use date#() instead of date()

[deepakk · Accepted solution]

hi,

Since you are getting Numberic data from your calendar objects, Create a Number date field at script level like

Num(Effective_start) as Effective_start_num,

Num(Effective_end) as Effective_end_num

This will make it easy set analysis caluclation

count ({<Effective_start_num = {'<=($(=vHistoryDateTo))'},Effective_end_num = {'>=($(=vHistoryDateFrom))'}>}distinct (dim_opportunity.crm_opportunity_id & dim_opportunity.Status))

I hope you got my point.

Deepak

[#8]

Hello , do you want me to use Date#  for the below expression, as I tried for a text box around the variable and it doesnt work?

Can you explain more please?

=count ({<Effective_start = {'<=date($(=vHistoryDateTo))'},Effective_end = {'>=date($(=vHistoryDateFrom))'}>}distinct (dim_opportunity.crm_opportunity_id & dim_opportunity.Status))

[#9]

Hello Deepak,

This worked as its hardcoded now, but what do you think was the problem converting the effective_start field to date and using it? I have used it for another app and it worked exactly as I wanted but here I have to stick to the numbers?

Can you explain a bit more as to if I have this effective start date linked to any other date which is in number format then would that cause a problem to this?? and also by using the num() can we trust the data what we see is correct?

Thanks,

Mady

[deepakk]

hi Mady,

Yes you can trust num and since its a numeric figure , filteration will be slightly faster.

You issue might be pattern of date format. Check the format of the date. for the variables and date field.

I alsways prefer numeric date field as it is easy to manage.