3 Replies Latest reply on Apr 12, 2019 12:42 PM by Ramzi El-Sayed

    LOD caluclations

    Radh Gowda

      I have 1000 customers   and need to find how many customers who purchased in 2015 were purchased in other years.


      Ex: Number of customers purchased in year 2015 is 800 and how many in those 800 (who purchased in 2015) are purchased in 2016, 2017 and so on.


      Can you please help me in deriving the LOD?






        • 1. Re: LOD caluclations
          Jim Dehner

          the solution to your problem will depend on the structure of your data and how complete the data set is (i.e. are the data sparse ) please attach a twbx workbook with the data (at least some representative)




          • 2. Re: LOD caluclations
            Radh Gowda

            Hi jim,

            thanks for the reply.

            Lets consider Sample super store data attached.

            I am able to find how many customers are purchased in year. As you see below there are 637 customer purchased in year 2013 and 693 were purchased in year 2014.

            Now my requirement is to find how many are purchased in 2013 (selected year) were also purchased in other years and their count. i.e. out of 637 in year 2013, how many were purchased on year 2012, 2011 and 2014?

            similary if i select year 2014 (see pic 2),out of 693 in year 2014, how many were purchased on year 2012, 2011 and 2013?



            pic 2:

            • 3. Re: LOD caluclations
              Ramzi El-Sayed

              Hi Radh,


              I had a hard time with this, the solution:


              create a parameter (Parameter 1) with the data type as an integer and add value 2011-2014 and input the same to display.


              create a calculation (Calc 1) as follows:

              { FIXED [Customer ID] : SUM(IF Year(Order Date)=[Parameter 1] THEN 1 END)}


              For the color (Color): create a boolean calc as DATEPART('year',[Order Date]) = [Parameter 1])


              Place the Calc in Row and set to CNT

              Order Date to Columns as Year


              Color to color mark


              Show parameter


              and voila.




              1 of 1 people found this helpful