Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 | ||
CustID | Date | Amount |
C001 | 10/10/2020 | $ 600 |
C002 | 8/8/2020 | $ 500 |
C003 | 7/15/2020 | $ 400 |
Previous Visit 1 | ||||
CustID | Date | Amount | Variance Days | Variance Amount |
C001 | 1/1/2020 | 500 | 283 | $ 100 |
C002 | 12/20/2019 | 400 | 232 | $ 100 |
C003 | 8/10/2019 | 300 | 340 | $ 200 |
Previous Visit 2 | ||||
CustID | Date | Amount | Variance Days | Variance Amount |
C001 | 6/25/2019 | 400 | 190 | $ 100 |
C002 | 5/5/2019 | 300 | 229 | $ 100 |
C003 | 1/1/2019 | 200 | 221 | $ 100 |
Any help in the right direction is appreciated.
Thanks
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.
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.
Hi Gary.
Thanks a million. Works brilliantly!!