Formula problem

[#1]

Good morning everybody,

I have a date created field which I have made the date and year in my script.

Date(DateCreated)                  as [Created Date],
Month(DateCreated)                as [Month],
Year(DateCreated)                  as [Year],

My current selection box is on November and 2013.

I want to calculate the amount of contracts terminated/disposed of in the month of November.

I have a variable - vMaxDate, which is max([Created Date]).

My formula to calculate the amount of terminated contracts is:

=count(if([Disposal Date]>=MonthStart(vMaxDate) or ([Disposal Date]<=vMaxDate),[Agreement Number]))

it gives me value of 9. But if I do a manual check on our operating system it should be 226.

Even if I change my formula to count(if([Disposal Date]>='01/11/2013' or ([Disposal Date]<='30/11/2013'),[Agreement Number]))  is still gives me 9.

What am I doing wrong?

Thanks

*R*

[jonathandienst]

Rentia

Assuming the first condition in your query was supposed to be [Created Date], I suggest the following:

=count(if([Created Date] >= MonthStart(vMaxDate)

  or ([Disposal Date]<=vMaxDate) or (Len([Disposal Date]) = 0),[Agreement Number]))

If performance of the above is too slow, you could convert that to a set expression,  like this:

=count({<[Created Date] = {">=$(=MonthStart(vMaxDate))"}, [Disposal Date] = {"<=$(=Date(vMaxDate))"}> +

  <[Created Date] = {">=$(=MonthStart(vMaxDate))"}, [Disposal Date] -= {"*"}>} [Agreement Number])

HTH

Jonathan

[peschu123]

I think an example is much more effective.

Could you check in your data if you perhaps just select the eliminated/disposed contracts, which have creation date in November too. As I understand you right, you want all disposed contracts of november no matter what creation date they have...

Regards,

Peter

[#4]

Hi Peter,

I have done a table box. And I have found the problem. It show me the diposed contracts that was created in November as well. It is not showing any contracts created prior to November.

How can I fix it, to show all the contracts terminated in November irrelevant of when the creation date was.

Thanks

*R*

[peschu123]

Hi Rentia,

the expression should look like this:

1.) count({$<Year=,Month=, [Disposal Date] = {">=$(vMinDate)<=$(vMaxDate)"}>}[Agreement Number])

2.) count({1<[Disposal Date] = {">=$(=$(vMinDate))<=$(=$(vMaxDate))"}>}[Agreement Number])

If it doesn't work, please clean up (load as numeric) date fields..

Explanation:

I added in the script a variable (vMinDate) with:

LET vMinDate= num(MonthStart(vMaxDate));

It's a matter of taste...But I think it's easier to use in set expression etc..

You should really take care of formatting datefields during load.

Please see attached qvw for more information. If you have any questions feel free to ask.

I hope it helps and best Regards,

Peter

[Anonymous]

You should use Dispose Date instead of Date Created in the expression and  issue will be solved.