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
          Jonathan Drummey

          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
              Jonathan Drummey

              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
              • 4. Re: Creating Abc placeholders data from nothing

                Thanks Jonathan, your post is very helpful.

                 

                I tired to recreate my data but but didn't manage to do so (your solution worked on my fictional data).

                 

                I use parameters because when using filters option N/A is displayed for null values and I don't want users to be able to choose this option. I don't use actions because there are many options to choose from and therefore they need to be in dropdown lists.

                 

                Could you quickly confirm that there is no way of excluding N/As from filters or creating dropdown actions?