Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

How do I calculate "time since first download"?

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.

Tags (2)
5 Replies
Employee
Employee

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

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

Regards,
Mike Tarallo
Qlik

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

How about this:

LOAD DISTINCT Customer,

          FirstSortedValue(Total Downloads,Date) as minDate

Resident downloadData

Where Not Match(Downloads, '0')

Group By Customer;

Not applicable

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

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

Employee
Employee

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

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"?

You can do it in the script, just like tko 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