4 Replies Latest reply on Feb 29, 2012 6:21 AM by paul.kretacz

# Creating Abc placeholders data from nothing

Hi,

in order to compute my table calculations I need to have data in all cells. Currently some don't show "Abc" placeholders (see attached).

Do you know any way of filling those cells with anything in a way that I don't have to restructure the database?

Thanks

Paul

• ###### 1. Re: Creating Abc placeholders data from nothing

LOOKUP(Aggregation([Field]),0) will cause Tableau to return Null when there is no data for a cell, so use that in replace of the Aggregation([Field]) that you'd normally use in your table calcs.

ZN(LOOKUP(Aggregation([Field]),0) if you need 0's instead. Note that putting the ZN() inside the lookup won't do what we want, because there is no data for the ZN() to be applied to until the LOOKUP() statement returns the Null.

Sample workbook is attached.

Jonathan

• ###### 2. Re: Creating Abc placeholders data from nothing

Hi Jonathan, thanks for your answer but for some reason this doesn't work in my workbook. Instead, I managed to overcome the problem by putting in the formula:

Calculation1 ->     if [Parameter1]=[Databasefield1] then [Sales] end

Calculation2 ->     if [Parameter2]=[Databasefileld2] then [Calculation1] end

and aggregating Calculation2 from there.

It works but the two problems I'm facing now are:

1). How to efficiently include many ifs (many filters) in one calculated field? - 'elseif' doesn't work because I want to be able to change any filter at any time and it seems that if one elseif is true, then it goes straight to the 'end'.

2). The calculation slowed down the workbook. Would you have any ideas how to improve the speed within these  calculations?

Thanks

Paul

• ###### 3. Re: Creating Abc placeholders data from nothing

As for why the ZN(LOOKUP([field],0)) didn't work, we'd have to get some sample data to figure out why. There are idiosyncracies to how Tableau pads out data that masters like Joe Mako are still trying to understand.

Here's an alternative calculation, using nested IF's:

IF [Parameter2] = [DatabaseField2] THEN

IF [Parameter1 = [DatabaseField1] THEN [Sales] END

END

Or, even faster:

IF [Parameter2] = [DatabaseField2] AND [Parameter1] = [DatabaseField1] THEN [Sales] END

At a minimum, using parameters causes a performance impact in Tableau because it can't do as much caching of results for the view from the DB or extract. A recommended alternative is to use dashboards with action filters, where instead of a parameter there's a view with the values and when the user clicks on one of those values the data view is updated.

In addition, the way you are using parameters causes even more impact because the parameter is being evaluated against non-aggregated data. Tableau has to return every row from the DB to test values in the IF statements. If there is any way to perform the IF statements against aggregated data, that will improve performance, sometimes radically.

Jonathan

1 of 1 people found this helpful