4 Replies Latest reply on Sep 28, 2017 9:06 AM by Jennifer VonHagel

    LOD expression with null values

    garth.conrad

      I have two tables, one containing "Sales" data and one containing "Issues" data.  The sales data table contains values for all items across all months (e.g. 0 when zero sold for the month).  The issues table only contains values (rows) for items that had an issue during that month. I am trying to create a rate calculation table that uses the count of the number of issues divided by the total sales for the item by year, by month. The issue I have is when I add a dimension (Code) to the table that is one level of detail below the common detail in each source table my calculation doesn't work.  I need a rate calculation of 'o.oooo' to be present in the months where an issue was not reported for an item.  I have tried blending the data sources, merging the data sources and forcing a union of the data sources. The same issue exists under each method of creating the data set.

       

      Example Data (excel) is attached and Tableau workbook (v10.3.3) is attached (this version of the workbook uses a forced union).

       

      Calculations:

      Units at Item Level = { FIXED [Common Item], YEAR([Common Date]), MONTH([Common Date]): sum([Units Adjusted]) }

      Units Adjusted = zn([Units])

      Issues = zn(COUNT([Code]))

      Rate = zn([Issues]/sum([Units at Item Level]))

        • 1. Re: LOD expression with null values
          Jennifer VonHagel

          Hi Garth, not sure I'm 100% following what you're going for, but I played around with the data and this is where I landed.

           

          It looks like it isn't possible to have records in Issues that are not in Sales. At least, in this sample dataset there isn't, so I made this a left join and joined on all the fields in common.

          I like your Common field calculations, they're great for an outer join. But if we only need a left join here, we can use the dimension columns from Sales where the two sources have common dimensions: Date, Item, Region.

           

          I set up this view in the most simple way to orient myself to your data. The Date, Item, and Region are from Sales.  I see that we have some Issues with no corresponding Units, and we have some Units with no corresponding issues (highlighted). The Rate evaluates to 0% in both these cases.

          Also, I see that with this set up in the most basic way, Units are definitely being counted multiple times.  I checked the source data (spreadsheet) for the total number of Units (15960) and total number of Issues (205), and see the grand total units in Tableau is way too high.

           

          Ok, let's check out some example records in your source data, and see if we can figure out how to get the right calculation so we don't multiple-count your Sales Units.  Here are matching records for Date/Item/Region in your two data sources.

          This is the outcome of joining them, if we throw in some SubTotals and the Total of SUM(Units) and COUNT(Code) we can see why Units gets multiple counted.

          We can create a formula so that we don't double count Units: SUM({ FIXED Date, Item, Region : max(Units) }). This will return 700 and allow us to calculate Issues/Units:

          But I'm not sure the rate works if we break it out by Issue Code; this is what we'd have. Is it correct to divide 3 and 1 by all 700 units? If that's ok, then great, just add Code into the table .

          Ok, so here is a screenshot with updated formulas in Tableau: The Rate is updated to have the new Units at Item/Date/Region Level as a denominator.

          I have attached the .twbx and my Excel Notes.

           

          Hope this helps,

          Jennifer

          • 2. Re: LOD expression with null values
            Jim Dehner

            Hi Jennifer - like your response - I had spent some time with this one also and was confused by what the goal was based on your observation about the data

            Good work

            Jim

            • 3. Re: LOD expression with null values
              garth.conrad

              Jennifer and Jim,

               

              I realize now the sample data I created doesn't reflect all the challenges with the real dataset I am working with. This issue I have is when I join the real data the underlying table increase to around 10M rows. This makes the viz a little bulky and slow. I was trying to create a more efficient join but it results in null values in the table since the data is not merged. Those nulls result in missing calculations in the viz.

               

              I have taken a different path on this for now. Thanks for the responses.

               

              G

              • 4. Re: LOD expression with null values
                Jennifer VonHagel

                Thanks Jim, and good luck, Garth!