6 Replies Latest reply on Jul 22, 2018 9:51 PM by Ellen Yeung

# Find difference if record in previous year doesn't exist (not just data is null)

Hello everyone,

I've attached a dummy workbook.

If there is no record for Netherlands in 2017 at all, not just null for a certain measure, how do I graph the difference in Sales \$ if I want to assume that there was \$0 Sales in 2017?  My formula for the Difference in Sales \$ is: ZN(SUM([Sales \$])) - LOOKUP(ZN(SUM([Sales \$])), -1)

When I display this in a text table, the difference appears but I want to be able to graph the difference.

• ###### 1. Re: Find difference if record in previous year doesn't exist (not just data is null)

I haven't tested it, but I think this should work:

ZN(SUM([Sales \$])) - ZN(LOOKUP(SUM([Sales \$]), -1))

• ###### 2. Re: Find difference if record in previous year doesn't exist (not just data is null)

Ellen,

Problem here is Chris McClellan's solution will give the difference for UK 2017 also along will Netherlands 2018.

I think you need to add dummy records in your table with all the distinct values of country and year with 0 sales. Then your formula will work. I will let you know if i find a better solution.

Thanks,

Ankit Bansal

• ###### 3. Re: Find difference if record in previous year doesn't exist (not just data is null)

I was already using the formula ZN(SUM([Sales \$])) - LOOKUP(ZN(SUM([Sales \$])), -1)

It does not work because the row for Netherlands 2017 doesn't exist.  It's not that Sales is Null for Netherlands 2017, but the record for Netherlands 2017 wasn't entered at all.  I may have only typed up 3 records in my sample dummy workbook, but for my real work situation, there are millions of records in our data warehouse.  So it's not like I can just create an empty row for Netherlands 2017 in the data table in real life.

• ###### 4. Re: Find difference if record in previous year doesn't exist (not just data is null)

I meant to say take all distinct combination of year and country from your dataset . And for all those combinations add dummy records with sale 0 or null.

Total no of dummy records will be = (total possible countries × total possible years)

So if you have 100 countries in your data and you are dealing with 10 years of data then you will have just 1000 dummy records.

• ###### 5. Re: Find difference if record in previous year doesn't exist (not just data is null)

Ummm ... now that I look at your workbook, there's no diff defined at all.

When I use your code, it works fine for the difference between 2017 & 2018 for the Netherlands.

• ###### 6. Re: Find difference if record in previous year doesn't exist (not just data is null)

Hi Chris,

When I moved the Year pill to the Columns shelf from the Rows shelf, the difference (using my formula) appeared for Netherlands on the bar chart, so that's what I did to resolve it.  Not sure why it doesn't work when the Year pill is on the Rows shelf.  I checked that the table calculation is Specific Dimension with year checked only.

Using your formula, it worked when I placed the Year pill on the Rows shelf.  However, if I were to add another row for e.g. Finland 2017 but no row for Finland 2018, the difference for Finland doesn't appear on the bar chart.

Thanks.