topic Re: Formula problem in QlikView

Formula problem

Wed, 04 Dec 2013 08:55:58 GMT

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*

Re: Formula problem

Wed, 04 Dec 2013 09:04:28 GMT

Try This

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

Re: Formula problem

sudeep_d — Wed, 04 Dec 2013 09:24:25 GMT

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

Re: Formula problem

MayilVahanan — Wed, 04 Dec 2013 09:28:12 GMT

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.

Re: Formula problem

Wed, 04 Dec 2013 09:52:19 GMT

Hi there,

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

Thanks

*R*

Re: Formula problem

Yousef_Amarneh — Wed, 04 Dec 2013 11:07:06 GMT

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

Re: Formula problem

Wed, 04 Dec 2013 11:11:14 GMT

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

Re: Formula problem

Wed, 04 Dec 2013 11:11:23 GMT

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

Re: Formula problem

Anonymous — Wed, 04 Dec 2013 11:16:41 GMT

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

Re: Formula problem

Yousef_Amarneh — Wed, 04 Dec 2013 11:23:47 GMT

Could you please upload the qvw file

Re: Formula problem

jonathandienst — Wed, 04 Dec 2013 11:30:19 GMT

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

Re: Formula problem

peschu123 — Wed, 04 Dec 2013 12:07:55 GMT

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

Re: Formula problem

Wed, 04 Dec 2013 12:58:05 GMT

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*

Re: Re: Formula problem

peschu123 — Wed, 04 Dec 2013 20:56:34 GMT

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

Re: Formula problem

Anonymous — Thu, 05 Dec 2013 04:51:22 GMT

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