Year-Over-Year Comparison For Missing Values

[mbespartochnyy]

Hello everyone,

I'm trying to compare amounts for few years of data. Attached is an app that I put together that shows the issue that I'm having. If you open up the app, you'll see that total amount for say 2019 is 2,100. For 2018, amount is 1,500. For 2017, amount is 2,450.

I'm working on a chart that would tell user the difference in amounts among years. I got 3 charts. One showing annual amount. Second showing breakdown of amounts by cost center and year. And third showing year over year difference by cost center.

When user select years 2019 and 2018, first chart will tell them that there's a difference of 600 between 2019 and 2018. Pretty straightforward. Next chart will show them which cost centers amounts are broken out by. Third chart, Difference By Cost Center, is the problem child. It's supposed to answer the question "Where is the difference coming from?" It does that but only for those cost centers that exist in both years...like cost center 0300:

Cost Center 300 had a difference of 100 between 2019 and 2018...thanks chart. What about cost center 0123 which had 1,000 increase in 2019 and cost center 0200 had a decrease of 500 in 2019?

I came up with a solution where I create fake data to have 0 amounts for cost centers that that don't exist for the year (uncomment the Workaround tab and run the script to see how I want Difference By Cost Center chart to look), but I'd rather not create fake data. Here's what I'm expecting Difference By Cost Center chart to display without generating fake data:

Does anyone have any suggestions for how to get that done?

Thanks in advance!

Mikhail B.

[mbespartochnyy · Accepted solution]

I ended up going the "create fake data" route. I can create an IF statement in expression that substitutes null with 0 when Below function returns 0, which will work for cases when there is data in 2019 but not 2018, but it will not work for cases where there was data in 2018 but not 2019. That being the case, I needed an actual row in the data model that expression can reference to execute calculation.

My actual example involved more than just a cost center and year, it had things like category, company, source type in addition to cost center along with few other fields that make a row unique so I had to do a slightly more complicated logic to make extrapolation work and not blow up the entire data set to go from 70K rows to millions.

Attached is a mimic of what I did in case someone will be facing similar issue. Cost Center and Workaround tabs are original script that I posted and the Suppliers and Supplier Workaround tabs resemble more closely the data set that I'm working with.