topic Aggregation within a dimension in QlikView

Aggregation within a dimension

richard_chilvers — Wed, 27 Aug 2014 13:26:29 GMT

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

Re: Aggregation within a dimension

MK_QSL — Wed, 27 Aug 2014 13:50:31 GMT

Provide few more data in excel file please..

Re: Aggregation within a dimension

jerem1234 — Wed, 27 Aug 2014 13:51:22 GMT

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!

Re: Aggregation within a dimension

richard_chilvers — Thu, 28 Aug 2014 07:53:55 GMT

Hi Jeremiah

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

Re: Re: Aggregation within a dimension

jerem1234 — Thu, 28 Aug 2014 13:10:53 GMT

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!

Re: Re: Aggregation within a dimension

richard_chilvers — Thu, 28 Aug 2014 13:16:39 GMT

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