5 Replies Latest reply: Aug 30, 2016 4:32 AM by Henric Cronström RSS

    How do I calculate "time since first download"?

    Alan Stickler

      I am trying to take a set of download data for different customers and realign the data set by "time since first download" instead of day of download.

      by example, I have data that looks like:

      CustomerDateDownloadsTime from first Download
      Customer 11/1/20151
      Customer 21/1/20150
      Customer 31/1/20150
      Customer 12/1/20151
      Customer 22/1/20151
      Customer 32/1/20150
      Customer 13/1/20150
      Customer 23/1/20151
      Customer 33/1/20151

       

      I was trying to use FirstSortedValue to find the First downloaded date with something like:

      LOAD

        DISTINCT Pharmacy,

        FirstSortedValue(Total Downloads,Date) as minDate

        Resident downloadData

        where Not Match(Downloads, '0');

       

      But it is not returning as expected.  I figured if I could calculate that value I could simple diff with the date and create a time from first download column needed above.

       

      Any help or direction would be appreciated.

        • Re: How do I calculate "time since first download"?
          Michael Tarallo

          Hello Alan - just wanted to let you know we got this discussion thread and I am attempting to find someone who may be able to tackle it. Do note others may respond as well.

           

           

          After you are satisfied, please mark the appropriate replies as CORRECT / HELPFUL so our team and other members know that your question(s) has been answered to your satisfaction.

           

          Regards,

          Mike Tarallo

          Qlik

          • Re: How do I calculate "time since first download"?
            Sunny Talwar

            How about this:

             

            LOAD DISTINCT Customer,

                      FirstSortedValue(Total Downloads,Date) as minDate

            Resident downloadData

            Where Not Match(Downloads, '0')

            Group By Customer;

              • Re: How do I calculate "time since first download"?
                Alan Stickler

                I tried that with no success.  The result is the following error:

                 

                Aggregation expressions required by GROUP BY clause: datesInfo: LOAD DISTINCT Customer, FirstSortedValue(Total Downloads,Date) as minDate Resident downloadData where Not Match(Downloads, '0') Group by Customer

                 

                I tried using min instead of FirstSortedValue and I was able to get results.  I am not sure I trust them and the min on the date gave me a number as a result.  In case it helps the input date is using the following formal (although I changed format multiple times) Date#([Date],'M/D/YY') as Date

              • Re: How do I calculate "time since first download"?
                Toni Kautto

                I would do a grouped load and aggregate the smallest date per customer. In the same table you can add the time calculation, in attached example I used today's date.

                • Re: How do I calculate "time since first download"?
                  Henric Cronström

                  You can do it in the script, just like Toni suggests, or you can do it in an expression. If you do it in the script, the calculation will be "static", i.e. you will have the same Min(Date) no matter what your selection is.

                   

                  However, if you instead do it in an expression, you will have it "dynamic", i.e. the Min(Date) will be evaluated based on the selection.

                   

                  To do it in an expression, use Customer and Date as dimensions, and use

                  Date(Min(total <Customer> Date))

                  to calculate "Date for first download".

                   

                  Time from first download can then be calculated using a simple subtraction:

                  Using Today() as reference: Today() - Min(total <Customer> Date)

                  Using the download date as reference: Date - Min(total <Customer> Date)

                   

                  HIC