3 Replies Latest reply: Mar 5, 2018 2:18 PM by David Forest RSS

    Active users from Start and End Date

    Alex Tomlins

      Hi Guys,

       

      I have been having some trouble. I have a table called CustomerSubscriptions and this has data that can be boiled down to

       

      Customer a, Product X, Manufacturer Y, StartDateTime, EndDateTime.

      Customer b Product Y, Manufacturer X,StartDateTime, EndDateTime.
      etc

       

      I want to be able to take the start date and end date and visualise a count of customers that were Live in reporting Months/Weeks/Days.

       

      I don't know if this is best done in the load or as a UI expression but it is difficult because i cannot have a month or day row for every month for every customer and so the it would need to dynamically work out whether the user rows were active depending on what dimensions splits were visualised on a graph. showing several counts of active subscribers per reporting month, week day etc.

       

      I have struggled with this for some time.

       

      Basically we need to have something that counts every customer's live period if in the dimension splits

        • Re: Active users from Start and End Date
          Ishtdeep Singh

          Please share your data or app.

           

          Thank you!

            • Re: Active users from Start and End Date
              Alex Tomlins

              I might be able to send a table with some personal details removed but generally I  can't  send any data if it's possible to explain the process or code snippets I would really appreciate It. Really though it's as I said customer I'd, product, manufacturer, startdatetime and end date time.

               

              It Is ok ked to a master calendar and there is a composite key involving product and manufacturer. I've simplified it because it's more the approach I'm after. It seems like it kgiht be something I need to do in an expression but maybe it's possible in the load. Trhe issue is that the data needs to be calculated dependent on the time dimension selected. So that to me says expression?

                • Re: Active users from Start and End Date
                  David Forest

                  Easiest way to me is to use IntervalMatch and create a flag for each date indicated a current customer for that date; then any slice of time would have a flag and can be rolled up to week, month, quarter, year.

                   

                  to your table, something like:

                  JOIN

                  IntervalMatch (Date)

                  LOAD StartDateTime, EndDateTime.

                  RESIDENT [CustomerTimes]

                  ;   

                   

                  JOIN

                  LOAD Customer,

                       Date,

                       1 as Active

                  RESIDENT [CustomerTimes]

                  ;