-
1. Re: Find difference if record in previous year doesn't exist (not just data is null)
Chris McClellanJul 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))
-
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
-
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.
-
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.
-
5. Re: Find difference if record in previous year doesn't exist (not just data is null)
Chris McClellanJul 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.
-
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.