Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

count days between a buy

Hello

I have a problem im not able to solve:

I want to Count how many days there have been before a user last bought something. So the dataset would look like this:

And i want to add the FlagDays column

DateCookieSessionFlagDays
2014-04-15AB0
2014-04-19AC4
2014-04-25AD6

How is this possible?

Tags (3)
1 Solution

Accepted Solutions

Re: count days between a buy

Use below script...

==========

Temp:

Load * Inline

[

  Date, Cookie, Session

  2014-04-15, A, B

  2014-04-19, A, C

  2014-04-25, A, D

];

NoConcatenate

Final:

Load

  Date,

  Cookie,

  Session,

  IF(Cookie = Previous(Cookie), Date - Previous(Date), 0) as FlagDays

Resident Temp

Order By Cookie, Date;

Drop Table Temp;

==========

6 Replies

Re: count days between a buy

Use below script...

==========

Temp:

Load * Inline

[

  Date, Cookie, Session

  2014-04-15, A, B

  2014-04-19, A, C

  2014-04-25, A, D

];

NoConcatenate

Final:

Load

  Date,

  Cookie,

  Session,

  IF(Cookie = Previous(Cookie), Date - Previous(Date), 0) as FlagDays

Resident Temp

Order By Cookie, Date;

Drop Table Temp;

==========

sujeetsingh
Honored Contributor III

Re: count days between a buy

use peek and iteration as

load

Date,Cookie,session

,interval(date-Previous(Date),'dd') as FlagDays

resident Table1

while (cokie<> previous(cookie);

\

Try something like this.

Not applicable

Re: count days between a buy

Sorry i think i deleted the post.

I have another question.

My data model is like this:

Hej:

Load

Cookie,

Date,

Sales,

Session,

Product

from QVD

Then i make a lot of temptables

like

TempDayInterval

Load distinct

Cookie as CookieTemp

,Session as SessionTemp

,Date as DateTemp

Resident Hej;

Then i make my new headtable

NoConcatenate

Sales:

Load

Cookie,

Date,

Sales,

Session,

Product,

if(???????????????????????????????????????) as FlagDay

Resident Hej;

How do i make my if statement Down here when my data model looks like this?

Sometimes i use if(exist(TempSession,Session), whatever......) as FlagWhatever

Re: count days between a buy

Try to use my solution in Sales table directly !

Not applicable

Re: count days between a buy

I already do that but the problem in the sales table is that i can only sort by date and not by cookie (because they some other calculations will be wrong)

- I just wanted to know if it was possible to do it from the temp table.

Re: count days between a buy

I don't have idea about your use of Temp Table... In this case, if you prepare some sample data file or apps, would be helpful to give you exact answer.

Community Browser