7 Replies Latest reply on Oct 1, 2013 12:47 PM by John Sobczak

# Count of Yearly Contracts Carrying Over from One Year to the Next

In the attached workbook, I'm tryig to do a count of contracts in each year that carried over from previous year as one metric and then use that metric to create a % of contracts from previous year.

• ###### 1. Re: Count of Yearly Contracts Carrying Over from One Year to the Next

This turns out to be a set of nested table calculations:

Previous Yr. Null?

IF ISNULL( LOOKUP( MAX( [Effective Year]  ),-1 ) ) THEN 0 ELSE 1 END

# Contracts Current Yr.

IF FIRST()=0 THEN WINDOW_SUM( SUM( [Number of Records] ) ) END

Prev Yr. # of Contracts

LOOKUP( [# Contracts Current Yr.],-1 )

# Carry Over Contracts

IF FIRST()=0 THEN WINDOW_SUM( [Previous Yr. Null?] ) END

# Contracts Current Yr.

IF FIRST()=0 THEN WINDOW_SUM( SUM( [Number of Records] ) ) END

% of Carry Over Contracts

[# Carry Over Contracts]/[Prev Yr. # of Contracts]

(See attached) Careful how you set the partitioning and addressing.

--Shawn

1 of 1 people found this helpful
• ###### 2. Re: Re: Count of Yearly Contracts Carrying Over from One Year to the Next

Shawn,

Thanks for your quick reply.  I've used your table calcs but am not quite all the way there yet.  Attached is the sample workbook using your table calcs (I looked at your sample WB but couldn't draw a connection - I'm slow).  Perhaps it is the partitioning or addressing, but that is what I struggle with.  I've tried many combinations with this new set of table calcs with no luck.

• ###### 3. Re: Re: Re: Count of Yearly Contracts Carrying Over from One Year to the Next

John, well it would help if I posted the correct workbook! Sorry about that. Look at this one and see if it makes more sense.

--Shawn

• ###### 4. Re: Count of Yearly Contracts Carrying Over from One Year to the Next

Thanks Shawn!

• ###### 5. Re: Count of Yearly Contracts Carrying Over from One Year to the Next

Shawn,

I attentively noticed and appreciated how carefully you set up those advanced table calcs for each.  One peculiar thing though in your workbook, is that when I remove the first table calc (# Carry Over Contracts) from label and re-pull it from measure list to place back on label, it doesn't reproduce the same result, even when I set the table calc at compute using Contract ID.   Can you remove and place back on this field with the same result as originally?

I noticed the previous year null is set up as an advanced table calc as well and when I changed this back to default "across table" I was sometimes getting back to your initial numbers - but this was very unstable and seemed to give me random results for the same actions.  I'm aware that when you change calculated fields (table only?) you need to swap them out from the shelves to be updated as existing fields on shelves do not always register with updates.  But I was careful to swap back in the table calc fields with the unstable results.

• ###### 6. Re: Count of Yearly Contracts Carrying Over from One Year to the Next

John sorry I missed this one last summer. But the reason you are getting the behavior you described is because it is a "nested table" calculation (using table calc inside a table calc). So when you first drag it onto the shelf and set the compute using you're getting a different answer because you haven't set the compute using on the other table calc:

If you set the default compute using in the table calc in the data window then this 'error' wouldn't happen.

Cheers,

--Shawn

1 of 1 people found this helpful
• ###### 7. Re: Count of Yearly Contracts Carrying Over from One Year to the Next

Thanks Shawn.  Someday I'll get my head wrapped around all this nesting, partioning and addressing.