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*

[#2]

Try This

=count({<[Created Date]={'>=$(MonthStart(vMaxDate)) <=$(vMaxDate)'}>}[Agreement Number])

[sudeep_d]

count({1}if([Disposal Date]>='01/11/2013' or ([Disposal Date]<='30/11/2013'),[Agreement Number]))

[MayilVahanan]

Hi

Try like this

=count({<[Created Date]={">=$(MonthStart(vMaxDate)) <=$(vMaxDate)", Month=, Year=}>}[Agreement Number])

And check the date format. If date format is mismatched, then it doesn't give correct answer.

[#5]

Hi there,

This gives me the all the terminations ever. Not just for November 2013.

Thanks

*R*

[Yousef_Amarneh]

your selection is November so the expression will give you the correct answer without using if statement or set analysis !!

[#7]

HI Yousef,

The reason I am setting an if statement is due to the fact that I have live and terminated inforamtion.

So I need the data where the status of the agreement is terminated.

I have tried the formula:

count(if(Status='T",[Agreement Number]) but that also only gives me a value of 9.

The reason I then decided to bring in the disposal date is because live data will not have a Disposal date.

Thanks

[#8]

Check your Disposal Date.
perhaps it is null. In this case you need to replace it with the alt() function to get a valid test.

Fabrice

[Anonymous]

To avoid date format errors ,

always create a numeric field eg... num([Created Date]) as [Created Date Num]

=count({<[Created Date Num]={'>=$(num(MonthStart(vMaxDate))) <=$(num(vMaxDate))'}>}[Agreement Number])

Regards

Nitin

[Yousef_Amarneh]

Could you please upload the qvw file