4 Replies Latest reply on Oct 11, 2016 8:01 AM by Shinichiro Murakami

# Parameters for blank columns

I have a situation where I have a sales metric for 2015 and want to use a parameter for my growth factor to predict 2016 sales.  I have a placeholder for 2016 in my raw data and want to figure out how I can create a calculation that will input my data into my dashboard.

I have attached my workbook and a screenshot of my dataset layout.  My dataset layout looks like this:

Year
Sales
Transactions
2015\$100050
2016

Ideally, I want worksheet to look like this using 10% as my sales growth from the parameter

Metric201520152016
Calculated Field\$1,000\$1,100

However, when I do this using a case statement, 2016 stays blank (putting year as my column)

Any help would be appreciated.

• ###### 1. Re: Parameters for blank columns

Hi William,

Could you try amending your calculation to do the below:

IFNULL(LOOKUP(sum([Sales])*(1+[Sales Growth]),-1),SUM([Sales]))

The issue in your calculation is that you are when you are multiplying the sales figure with the parameter it is still looking at the 2016 value - which is null. That is why null is being returned.

Thanks,

Ben

• ###### 2. Re: Parameters for blank columns

William

But

If in a case you want to compare i.e. 2017 with 2015, you can use below formula as well.

Only a slight change of your original formula

[Calculation1]

case [Year] when 2015 then [Sales] else {fixed:sum(if [Year]=2015 then [Sales] end)}*(1+[Sales Growth]) end

Thanks,

Shin

• ###### 3. Re: Parameters for blank columns

It depends whether you are ok to hard code the year values in your calculation... This is why I suggested the IFNULL and lookup calcs (to avoid hard coding).

• ###### 4. Re: Parameters for blank columns

Exactly.

You are 100% correct.

Shin