Membership Graph from renewal sales

[#1]

Hi

I am new to Qlik and want to create a graph of membership. I have three tables below plus a Master Calander as in the "Become QlikView Developer from Scratch" course. Tables below are really much bigger of course. The site has been running for around 15 years and has around 1000 members in any one year.

Customer

CustomerId	Name
1	John Blogs
2	Jane Jones

Sales

OrderID	CustomerID	ProductID	Order Date
1	1	1	19.10.2016

Product

ProductId	Renewal Months
1	12
2	0

Renewal Months is usually 0, but for some products is 1,3 or 12 to indicate a renewal for 1,3 or 12 months.

So to draw the graph of membership over time, I first thought of using set analysis in the create graph dialogue, but after some while, I decided that was a little inefficient and the best thing was to create a membership table. Having a one-month granularity should be fine so I considered something like

CustomerId	Date
2	12/2004

Where the dates represent months, and there is one row for each month a customer has been a member. Now drawing the graph is easy as for each month in the time dimension I just count the matching entries in the table.

The problem is that I can not seem to come up with a way of creating this table. Any help would be appreciated.

Edit PS:

To be more explicit: I want to add a row to the Memebership table for (customer, month) if

• The customer has bought a product with [Renewal Months] = 1 during month.
• The customer has bought a product with [Renewal Months] = 3 during month or one of the previous two months.
• The customer has bought a product with [Renewal Months] = 12 during month or one of the previous eleven months.

[effinty2112 · Accepted solution]

Hi Piers,

Try:

Customers:

LOAD * INLINE [

    CustomerID, Name

    1, John Blogs

    2, Jane Jones

];

Sales:

LOAD * INLINE [  

    OrderID, CustomerID, ProductID, Order Date

    1, 1, 1, 19.10.2016

];

Products:

LOAD * INLINE [

    ProductID, Renewal Months

    1, 12

    2, 0

];

TempMonths:

LOAD OrderID, ProductID,[Order Date] ,

Date(Monthstart(Date([Order Date])),'MM-YYYY') as Month

Resident Sales;

Left Join(TempMonths)

LOAD *  Resident Products;

Drop fields ProductID, [Order Date] from TempMonths;

for i = 1 to NoOfRows('TempMonths')

Let vNoOfMonths = FieldValue('Renewal Months',$(i));

if $(vNoOfMonths) >0 then

Months:

LOAD

FieldValue('OrderID',$(i)) as OrderID,

Date(AddMonths(FieldValue('Month',$(i)),RecNo()-1),'MM-YYYY') as Month

AutoGenerate $(vNoOfMonths);

End if;

Next i;

DROP Table TempMonths;

cheers

Andrew

[#3]

Thanks Andrew

I wanted a table of Customers vs months not orders vs month, still you put me on the right track. The solution below seems to work. However, I think it is particularly inefficent. I'll need to give some thought to imporving that.

Piers

//*******************************************

Customers:

LOAD * INLINE [

    CustomerID, Name

    1, John Blogs

    2, Jane Jones

];

Products:

LOAD * INLINE [

    ProductID, Renewal Months

    1, 12

    2, 0

];

MapMembershipMonths:

Mapping Load ProductID, [Renewal Months] Resident Products;

Sales:

LOAD

*,

ApplyMap('MapMembershipMonths', ProductID, 0) as [Product Membership];

LOAD * INLINE [

    OrderID, CustomerID, ProductID, Order Date

    1, 1, 1, 19/10/2016

];

TempSales:

LOAD

CustomerID,

[Order Date],

[Product Membership],

Date(Monthstart(Date([Order Date])),'MM/YYYY') as Month_temp

Resident Sales;

Membership:

Load * Inline [dummy];

For row = 0 to NoOfRows('TempSales')-1

Let numMonths = peek('Product Membership', $(row), 'TempSales');

//Need to know that numMonths can never be <null>

If $(numMonths) > 0 THEN

Membership_part:

LOAD

peek('CustomerID', $(row), 'TempSales') as CustomerID,

Date(AddMonths(peek('Month_temp', $(row), 'TempSales'), RowNo()-1),'MM/YYYY') as [Membership Month]

AutoGenerate $(numMonths);

Concatenate(Membership)

Load * Resident Membership_part;

drop table Membership_part;

End if

Next row;

Drop Fields dummy from Membership;

Drop Table TempSales;

[effinty2112]

Hi Piers,

This is the data model created by my script:

Customers are associated with months via the Sales table.

Chart Count(distinct CustomerID) against Month.

cheers

Andrew

[#5]

Ahhhhhhhhhhhhhh! I see. *Face Palm*

[#6]

Andrew

I tried your solution on some real data, and I needed some tweaks.

1) FieldValue did not seem to work, but peek did. So I changed FieldValue to peek and started the for loop at 0 rather than 1. Not sure I really understand what is going on here.

2) One of the orders returned null for 'Renewal Months'. A data problem, but I added a check for 'If not IsNull(vNoOfMonths)'.

3)  What to do with future data? I  added the following to lop off the future part of the table.

Membership:

LOAD   OrderID,   Month as [Membership Month]

Resident Membership_temp   

where Month< date(today(0),'MM/YYYY');   

drop Table Membership_temp;

Piers