Locking a Set Analysis Result in a table like Excel $

caseyjohnson — Fri, 27 Jul 2018 16:32:34 GMT

I have a simple table and I'm trying to figure out how to lock in a calculation using set analysis (or something else?) for the calculation of other fields. So far, I'm striking out. What I'm trying to do is use a certain field like you would in Excel (ex: $C$15) to make some calculations throughout the table.

I'd like to be able to use the 2018 Avg Sales to calculate the percent changes throughout the table without hard setting a value in the calculation. Would need it to be able to accept Dimension changes as well.

Table:

Year Sum(Sales) Avg(Sales) Percent Change (in Avg)

2018 20000 500 0%

2017 17500 425 ?

2016 15000 413 ?

2015 13000 435 ?

This was my attempt:

Avg(Sales) - Avg($< Year = {2018}>} Sales) / Avg($< Year = {2018}>} Sales)

So for 2017, it should show a (-15%) change and so on.

Re: Locking a Set Analysis Result in a table like Excel $

sunny_talwar — Fri, 27 Jul 2018 16:35:39 GMT

There are two ways you can do this... if you always want to divide by 2018's avg sale... try this

Avg(Sales)/Avg(TOTAL {$<Year = {2018}>} Sales) - 1

If you want to divide by the avg sales of next year, then try this

Avg(Sales)/Above(Avg({<Year>}Sales)) - 1

Re: Locking a Set Analysis Result in a table like Excel $

caseyjohnson — Fri, 27 Jul 2018 17:22:06 GMT

That's it! Thank you Sunny!!

topic Re: Locking a Set Analysis Result in a table like Excel $ in App Development

Locking a Set Analysis Result in a table like Excel $

Re: Locking a Set Analysis Result in a table like Excel $

Re: Locking a Set Analysis Result in a table like Excel $