8 Replies Latest reply on May 8, 2018 8:38 AM by Cerys Heath

# Null values in running total appear at start date - how to move to end?

I am trying to make a retention chart - a running percentage of how many customers have renewed, throughout the year. I have a data set which looks something like this:

Customer IDDate renewed
124/03/2016
2[Null]
301/05/2016
etc

I've been able to get a cumulative running percentage by adding a Table Calculation to do Running Total (Number of Records) and then edit that to add a secondary calculation: Percent of Total

However, all the null values seem to go in at the start of the year, so it looks like we begin on e.g. 30% renewed and then this gradually rises to 100% at the end of the year. I want the null values to count as not renewed, i.e. the year should end at 70%.

Is there a way to do this? I've attached sample data and a sample workbook.

• ###### 1. Re: Null values in running total appear at start date - how to move to end?

I think all you need to do is change the calculation to avoid nulls.  Without your workbook being here I can only guess that you need to say something like 'IF NOT ISNULL([RENEWAL DATE]) THEN...

• ###### 2. Re: Null values in running total appear at start date - how to move to end?

Sorry, thought I had attached a workbook, I've edited it in now.

I'm not sure how to finish writing the calculation you've suggested, but I think excluding the nulls will mess up the percentage of total part of the calculation. I need this percentage to include the nulls, so that it is clear how many customers haven't renewed, as well as those who have.

• ###### 3. Re: Null values in running total appear at start date - how to move to end?

These nulls will go away as dates are filled in. I will complete this idea and send to you this evening. Due to security here, I am unable to send anything out.

Sent from the iPhone of Timothy Dines.

• ###### 4. Re: Null values in running total appear at start date - how to move to end?

Not everyone will renew during the course of the year, and more importantly, I want the chart to display the percentage who have yet to renew - so this really isn't a useful route to go down for me. Thank you for looking into it though.

• ###### 5. Re: Null values in running total appear at start date - how to move to end?

You might consider a dual axis chart that show the renewals on one and the yet to renew on the other.

Sent from the iPhone of Timothy Dines.

• ###### 6. Re: Null values in running total appear at start date - how to move to end?

Hi Cerys, hi Tim,

I have had a go at the problem, and I solved it to some extend with a fixed expression on labels :

SUM([Row Count]) / sum({FIXED : SUM([Row Count])})

I think if we have several years this would not work correctly as I assume one wanted to calculate for each year. I have not had a thought about that yet. Also, I could not change the display for the null values as "no renewal", I had to make the tool tip more informative.

I have attached my workbook. - Hope that helps a bit.

Natalia

• ###### 7. Re: Null values in running total appear at start date - how to move to end?

I just forgot to add. I have used a continuous date and then displayed it in a discrete way. Like this one can manually sort the null at the end of the chart.

This is how:

Hope that helps you move forward a bit more! -Natalia

• ###### 8. Re: Null values in running total appear at start date - how to move to end?

Hi Tim, Natalia,

Thank you both for your help. I think I've now found a solution which matches what I had in mind originally.

Here were the steps I took, in case anyone else needs to do something similar. I've also attached an updated copy of my sample workbook with these steps carried out.

1. Create a calculated field "Renewed?", as follows:

IF NOT ISNULL([Date]) THEN "Renewed" ELSE "Didn't Renew" END

2. Switch the chart type to 'Area (continuous)'

3. Drop the field 'Renewed?' onto the color. If necessary, use the legend to ensure that 'did not renew' is sorted to appear at the top of the chart.

4. Edit the table calculation so that the percent of total is computed using 'Renewed'