7 Replies Latest reply on Oct 10, 2017 7:31 AM by Norbert Maijoor

    Count of days active.

    Sean Conner

      Hi Everyone,

       

       

      I have customer ID and Order date. I want to find the number of days active for each customerID.

       

       

      I have attached the sample workbook for reference taking superstore data.

       

       

      I have plotted customerID and Day(order date), so that I shows the customer ID and the active days.

       

       

      For instance, the output I'm looking for is that of the first customerID is active on 5 days of the month. So I want to display the active days as 5 for first customer. Subsequently I want to display for all the customerID's.

       

       

      Thanks,

      Sean.

        • 1. Re: Count of days active.
          Norbert Maijoor

          Hi Sean

           

          Count distinctive active order days: {fixed [Customer ID]:countd([Order Date])} will do the job....

           

          Regards,

          Norbert

          1 of 1 people found this helpful
          • 2. Re: Count of days active.
            Sean Conner

            Hi Norbert,

             

            Now that I have what I want. There is an extension for it.

             

            Now I am showing the number of active days, how can we show the number of active days in the first five days and the last five days of the month. Meaning previously we have calculated the active days for a month. Now I want to calculate the number of active days in the first five and last five days of the month.

             

            Month will have 30 days and sometimes 31 days, so the calculation has to be dynamic.

             

            Please help.

             

            Thanks,

            Sean.

            • 3. Re: Count of days active.
              Norbert Maijoor

              Hi Sean,

               

              Find my approach as reference below and stored in attached workbook version 10.3 located in the original thread.

               

               

               

              1. max date: {fixed [Order Date (Month / Year)]:max([Order Date])}

               

              2. min date: {fixed [Order Date (Month / Year)]:min([Order Date])}

               

              3. counter:([first & last 5 days])

               

              4. Drag required objects to the indicated locations.

               

               

              Regards,

              Norbert

              • 4. Re: Count of days active.
                Sean Conner

                Hi Norbert,

                Thank you for the workbook. I have had a look at it.

                I am attaching my workbook to the original thread on which I am working on.

                There are five sheets,

                View - which is an overview

                Number of active days - displaying  the number of active days

                First five days - shows the active days in the first five days

                Last five days - shows the active days in the last five days

                 

                I want to display CustomerID, Number of active days,First five days and last five days in a single view.

                Please help.

                 

                Thanks,

                Sean.

                • 5. Re: Count of days active.
                  Norbert Maijoor

                  Hi Sean,

                   

                  Upfront we have to clear one thing first

                   

                  The definition of first and last five days.

                   

                  a. 1-##-20## till 30-##-20## or 31-##-20##  or 28-##-20## or 29-##-20## (id this is the  case we should use the "scaffolding Technic"  where add a "backbone" to the dataset with all available dates

                   

                  or

                   

                  b. dates available in de underlying database

                   

                   

                  Hope to hear from you.

                   

                  Regards,

                  Norbert

                  • 6. Re: Count of days active.
                    Sean Conner

                    Hi Norbert,

                     

                    Let me explain in detail what I am looking for.

                     

                    I am trying to find the number of days worked for each customerID in a month. With your help, I've achieved that.

                     

                    Now I want to see for each customerID, if they have worked on first five days of the month. Meaning, every month starts from 1 till 31. So, from Day1 till Day5 of the month(which is 5 days) I want to know how many days they have worked.

                     

                    The same goes for the last five days as well. From Day26 till Day 31 of the month(which is again 5 days) I want to know the no. of days worked.

                     

                    Please write to me if you need more clarification on this.

                     

                    Thanks a bunch,

                    Sean.

                    • 7. Re: Count of days active.
                      Norbert Maijoor

                      Hi Sean,

                       

                      Find my updated approach below as reference and stored in attached workbook version 10.3 located

                       

                       

                      Define a scaffold file and define it as the primary datasource. Define the relationship on Date-Order Date

                       

                       

                      2. Define the calculated field Max Date in the  primary datasource: {fixed [date (Month / Year)]:max([date])}

                       

                      3. Define the calculated field Min Date in the  primary datasource: {fixed [date (Month / Year)]:min([date])}

                       

                      4. Define the calculated field First & Last 5 days:

                      if DATEDIFF('day',([min date]),([date]))<=4

                      or DATEDIFF('day',([max date ]),([date]))>=-4

                      then  [date]  end

                       

                       

                      5. Drag required objects to the indicated locations and exclude NULL from First & Last 5 days...

                       

                       

                      Regards,

                      Norbert