Aggregation within a dimension

[richard_chilvers]

I have a table which shows purchases for individual products as follows:

PRODUCT  Purchase#  Purchase Date

PR1               PURCH1         1/1/12

PR1               PURCH15        2/1/12

PR1               PURCH17        14/1/12

PR1               PURCH31         20/2/12

PR2               PURCH2          1/1/12

PR                 PURCH21        17/1/12

etc.....

I wish to add a column for each purchase which shows the days between the 1st (ever) purchase of that product and the Purchase Date.

Eventually I want to drop Purchase# as a dimension and simply show each PRODUCT with the number of purchases within 30 days of the 1st sale.

Ideas please.

Richard

[MK_QSL]

Provide few more data in excel file please..

[jerem1234]

Try this has your expression in your chart:

[Purchase Date] - min(total <PRODUCT> [Purchase Date])

That should get you the number of days between the first purchase of the Product and the Purchase date.

Hope this helps!

[richard_chilvers]

Hi Jeremiah

That's what I thought - and I'd already tried it, but I simply get null values because the min function fails.

[jerem1234]

Hmm seems to be working in my qvw. Maybe your dates are strings and not actually dates? I have attached an example of what you are trying to do and also the extra bit about being less than 30 days from first purchase date.

Hope this helps!

[richard_chilvers]

Hi

Dates always make life tricky ! Thanks for your help, maybe my date format was not quite right , but with your help I now have it working.

Regards