7 Replies Latest reply on Oct 2, 2018 5:52 AM by Imran Azam

HI guys

I am working on urgent work task trying to count the number of over new contracts, how many repeat and how many lapse.

i have the data below ( i hjave attached my workbook too)

account
start date
end date
ab27/04/201726/05/2018
ab19/07/201727/07/2018
ac27/04/201526/03/2018
ac27/06/201526/05/2018

i have linked this to a separate sheet contain daily dates so i can get the dates between and also i can use to display the dates across , i have called this months

• each account can have more that 1 contract but i want to count all the contracts  under an account as 1, using the min star date and the max end date, so in this case for account ab the contract start date would be  27/04/2017 and end date would be 27/07/2018
• contract lapse on the max end date
• repeat are the dates between the start and end date.

so for account ab the contract starts 27/04/2017  s new should be shown at FY 2016-17 , and contract ends 27/07/2018 so it should show lapsed fy 18-19

rest should be like below

please note my FY are set to july-june

statusFy-14-15Fy-15-16Fy-16-17Fy-17-18fy-18-19
new11
repeat111
lapse11

i am having major problems doing this my results do not seem to be right ( checked the attached workbook ) i don't know if it is my calcultion field?

can anyone help me on this?

if this is not clear i am happy to provide any other info.

thank you guys

 27/04/2017
• ###### 1. Re: Help with calculation please

Hi Imran,

I think, that is what you are looking for.

The main problem you had - calculated field wasn't recognizing fiscal year configuration. You need to put the logic into calculation. Below is the screen how to do it straight forward.

Also, find workbook with calculation attached.

Please, let me know, if you have any questions.

Thanks,

Vera

• ###### 3. Re: Help with calculation please

this looks correct can you please explain how you did this?

• ###### 4. Re: Help with calculation please

Hi,

I've created additional calculated field startdatemin with logic similar to endatemax.

Other changes were made in "status" calculation. before making the comparison, By nesting a DATEADD within a DATEPART, calculate the fiscal year for any date by first subtracting three months from the date in question.

Thanks,

Vera.

• ###### 5. Re: Help with calculation please

HI this works bit when i add other data in ( as this was just a small example it doesnt full work

if you have something like an email i can email you the whole thing