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)

Ellen Yeung Jul 20, 2018 6:10 PM

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.

Thanks for your help!

Missing Record for Netherland 2017.twbx 35.0 KB

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

Chris McClellan Jul 20, 2018 6:20 PM (in response to Ellen Yeung)

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

ZN(SUM([Sales $])) - ZN(LOOKUP(SUM([Sales $]), -1))
Like Show 0 Likes(0)

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

Ankit Bansal Jul 20, 2018 9:52 PM (in response to Ellen Yeung)

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
Like Show 0 Likes(0)

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

Ellen Yeung Jul 22, 2018 1:41 AM (in response to Chris McClellan)

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.
Like Show 0 Likes(0)

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

Ankit Bansal Jul 22, 2018 2:31 AM (in response to Ellen Yeung)

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.
Like Show 0 Likes(0)

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

Chris McClellan Jul 22, 2018 3:19 AM (in response to Ellen Yeung)

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.
Like Show 0 Likes(0)

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

Ellen Yeung Jul 22, 2018 9:51 PM (in response to Chris McClellan)

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.
Like Show 0 Likes(0)

Actions

Go to original post

Tableau Community Forums

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

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

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

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

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

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

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

Actions

More Like This