Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
QlikNewbie20
Contributor II
Contributor II

Display /Calculate Customer Trend

Hi Qlik Experts

I need some help on splitting up customers by visit and measuring parameters of visit from  the base table

I would like to create a base table of distinct customers that have visited in the last 6 months with three fields: CustID,Date, Amount from a mastertable

I would like to use those unique customers and identify their previous visit and identify the difference in days and amount spent. I then would like to do the same for the visit prior to that as well.

Something like this is what I'm trying to achieve

Last Visit
CustIDDateAmount
C00110/10/2020 $       600
C0028/8/2020 $       500
C0037/15/2020 $       400

 

Previous Visit 1
CustIDDate AmountVariance DaysVariance Amount
C0011/1/2020500283 $          100
C00212/20/2019400232 $          100
C0038/10/2019300340 $          200

 

Previous Visit 2
CustIDDate AmountVariance DaysVariance Amount
C0016/25/2019400190 $       100
C0025/5/2019300229 $       100
C0031/1/2019200221 $       100

 

Any help in the right direction is appreciated.

Thanks

1 Solution

Accepted Solutions
GaryGiles
Specialist
Specialist

You want to make use of the FirstSortedValue function.

For Last Visit table
Add CustID as a dimension

For Date, use the following in a Measure:
FirstSortedValue(Total <CustID> Distinct Date(Date), -Date,1)

For the Amount, use the following in a Measure
sum(if(Date=aggr(FirstSortedValue(Total <CustID> Distinct Date(Date), -Date,1),CustID,Date),Amount))

For Previous Visit table
Date measure becomes:
FirstSortedValue(Total <CustID> Distinct Date(Date), -Date,2)

Amount becomes:
sum(if(Date=aggr(FirstSortedValue(Total <CustID> Distinct Date(Date), -Date,2),CustID,Date),Amount))

Variance Days:
FirstSortedValue(Total <CustID> Distinct Date(Date), -Date,2)
- FirstSortedValue(Total <CustID> Distinct Date(Date), -Date,1)

Variance is:
sum(if(Date=aggr(FirstSortedValue(Total <CustID> Distinct Date(Date), -Date,2),CustID,Date),Amount))
- sum(if(Date=aggr(FirstSortedValue(Total <CustID> Distinct Date(Date), -Date,1),CustID,Date),Amount))

You should be able to take it from here to create Previous Visit 2.  The key change is the number in bold.  It indicates an offset from the first item in the sorted date list.

Using a variable for the offset would allow you add the ability make the visit history more dynamic.

Hope this helps.

View solution in original post

2 Replies
GaryGiles
Specialist
Specialist

You want to make use of the FirstSortedValue function.

For Last Visit table
Add CustID as a dimension

For Date, use the following in a Measure:
FirstSortedValue(Total <CustID> Distinct Date(Date), -Date,1)

For the Amount, use the following in a Measure
sum(if(Date=aggr(FirstSortedValue(Total <CustID> Distinct Date(Date), -Date,1),CustID,Date),Amount))

For Previous Visit table
Date measure becomes:
FirstSortedValue(Total <CustID> Distinct Date(Date), -Date,2)

Amount becomes:
sum(if(Date=aggr(FirstSortedValue(Total <CustID> Distinct Date(Date), -Date,2),CustID,Date),Amount))

Variance Days:
FirstSortedValue(Total <CustID> Distinct Date(Date), -Date,2)
- FirstSortedValue(Total <CustID> Distinct Date(Date), -Date,1)

Variance is:
sum(if(Date=aggr(FirstSortedValue(Total <CustID> Distinct Date(Date), -Date,2),CustID,Date),Amount))
- sum(if(Date=aggr(FirstSortedValue(Total <CustID> Distinct Date(Date), -Date,1),CustID,Date),Amount))

You should be able to take it from here to create Previous Visit 2.  The key change is the number in bold.  It indicates an offset from the first item in the sorted date list.

Using a variable for the offset would allow you add the ability make the visit history more dynamic.

Hope this helps.

QlikNewbie20
Contributor II
Contributor II
Author

Hi Gary.

Thanks a million. Works brilliantly!!