5 Replies Latest reply on Apr 4, 2017 6:05 PM by Vandana Samtani

    Counts With date conditions

    Vandana Samtani

      Hi All,

       

      I am working on a data set to figure out top shows  that the user watched in last 7 days and will be refreshed every day.

      Can you please give any suggestions?

      Thank you,

      Vandana

       

      Below is the data set -

       

         

      Show NameUserDate
      show 113/2/2017
      Show 213/1/2017
      show 142/28/2017
      Show 352/27/2017
      Show 512/27/2017
      show 122/26/2017
      Show 212/25/2017
      show 133/1/2017
      Show 312/24/2017
      Show 522/25/2017
      show 142/23/2017
      Show 212/22/2017
      show 152/21/2017
      Show 312/26/2017
      Show 513/1/2017
      Show 423/2/2017
      Show 623/3/2017
        • 1. Re: Counts With date conditions
          Mia Lee

          Hi, Vandana,

           

          1. create a calc field that writes (dim)

          T7D_user

          IIF(datediff('day',[Date],today())<=7,user,null)

           

          2. create a calc field that writes

          #of T7D_user

          countd(T7D_user)

           

          3. place ShowName dimension and #of T7D_user measure and sort in desc. order.

           

          Hope this helped.

          Thanks,

           

          Mia

          • 2. Re: Counts With date conditions
            Deepak Rai

            Hi Vandana,

            Please see attached and screenshot.

            Thanks

            Deepak

             

            1 of 1 people found this helpful
            • 3. Re: Counts With date conditions
              Vandana Samtani

              Hi Mia,

               

              Quick Quesiton on Date Differrence - I am calculating counts 30 days prior if a user selects any date from the parameter.

               

              For e.g. - User selected 03/05/15 then the caculation should give counts for 30 days prior from 03/05/2015

               

              This is what I am using for Past 30 days calculation

              IIF(DATEDIFF('day',[Select Date],[Subscription Date]-30) <=30,[External User Id],null)

               

              Then Count(Past 30 Days)

               

              The business requirement is to calculate counts for prior 30 days from the date user selects from parameters.

               

              Thanks for your help,

              Vandana

              • 4. Re: Counts With date conditions
                Mia Lee

                Hi, Vandana

                 

                I am not quite understanding what you're asking is exactly. You can use the same algorithm I gave you, just add parameter(UserSelection) and write,

                 

                1. create a calc field that writes (dim)

                T7D_user

                IIF(datediff('day',[Date],Param_UserSelection)<=30,user,null)

                 

                2. create a calc field that writes

                #of T7D_user

                countd(T7D_user)

                 

                3. place ShowName dimension and #of T7D_user measure and sort in desc. order.

                 

                Hope I understood your question correctly.

                Thank you.

                • 5. Re: Counts With date conditions
                  Vandana Samtani

                  Thanks Mia!

                   

                  I have the same logic that you recommended. Just replaced the date condition with parameter and it will give the counts for last 30 days.

                   

                  Thanks again for all your help,

                  Vandana