2 Replies Latest reply on Feb 28, 2017 3:51 PM by Dan McCall

    Level of detail calculation year over year

    Dan McCall

      Hello,

       

      I've been working on moving an excel analysis over to Tableau and I'm having trouble with some level of detail calculations.

       

      I often work with large contract-level data sets and I like to calculate new/lost/existing at various aggregation levels--most often at the customer level. In this example I've attempted to trend the "lost customer" amount over time, where a "lost customer" is defined as a customer that had an amount in the prior year and nothing in the current year. I'm able to perform this calculation for each customer, but I'm having difficulty aggregating the calculation.

       

      I tried to use INCLUDE to set the level of detail at the Customer level, but the totals still don't work. When I try to create the LOD on the outside of the IF statement, I get the error "level of detail expressions cannot contain table calculations..." Is there a way to get the prior year amount at the customer level without using table calculations and without changing the data structure?

       

      I'd appreciate any help I can get with this. Thank you!

       

       

       

        • 1. Re: Level of detail calculation year over year
          Shinichiro Murakami

          DAn,

           

          I'm not sure the difference between "lost" and "Year over Year" difference.

          could you clarify the definition of "lost" one more time?

          Maybe I'm confused with aggregation level. 

          Is that really Customer level or Customer x Product??

           

           

          Thanks,

          Shin

          • 2. Re: Level of detail calculation year over year
            Dan McCall

            Shin,

             

            "Lost" is intended to be the prior year amount * -1 of those customers that had sales in the prior year but have zero sales in the current year. I would like to classify those customers that report a decline in sales from prior year to the current year, but are not at zero separately. The difference between the prior year and the current year would include all customer changes (new, lost, increase, and decrease), whereas in my analysis I would like to separate these categories. However, the classification should be performed at the customer aggregation level.

             

            Thanks,

             

            Dan