How do I calculate "time since first download"?

[#1]

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:

Customer	Date	Downloads	Time from first Download
Customer 1	1/1/2015	1
Customer 2	1/1/2015	0
Customer 3	1/1/2015	0
Customer 1	2/1/2015	1
Customer 2	2/1/2015	1
Customer 3	2/1/2015	0
Customer 1	3/1/2015	0
Customer 2	3/1/2015	1
Customer 3	3/1/2015	1

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.

[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

[sunny_talwar]

How about this:

LOAD DISTINCT Customer,

          FirstSortedValue(Total Downloads,Date) as minDate

Resident downloadData

Where Not Match(Downloads, '0')

Group By Customer;

[#4]

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

[ToniKautto]

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.

[hic]

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