topic cross tab analysis help in QlikView

cross tab analysis help

Thu, 20 Jan 2011 18:33:42 GMT

Hello,

I need some help in determining how to produce the following report pasted below. This report shows the number of customers that had a specific product (Video, HSD, CDV etc.) in Month 2 but have either changed their product set in Month 7 or have the same product in Month 7.

Example (cells in blue):

2,052 customers had Video Only in Month 2 and have Video Only in Month 7.

13 customers had HSD Only in Month 2 but have Video Only in Month 7.

32 customers had Video Only in Month 2 but have HSD Only in Month 7.

What I need to do is track the same set of customers and their product(s) in Month 2 and Month 7, in order to compare how their product set has changed from Month 2 to Month 7.

	LOB Comparison: Month2 by Month7 (Raw Counts)
		Month 7
	Month 2	No Service	Video Only	HSD Only	CDV Only	Video & HSD	Video & CDV	HSD & CDV	Video+HSD+CDV
No Service	565	531	2	10	1	10	2	4	5
Video Only	2,573	97	2,052	32	0	151	12	2	227
HSD Only	12,145	906	13	10,471	0	600	1	93	61
CDV Only	1,312	182	0	0	1,077	0	10	21	22
Video & HSD	14,653	788	443	249	2	12,291	5	8	867
Video & CDV	579	37	5	0	5	2	418	7	105
HSD & CDV	5,094	561	2	50	411	9	23	3,643	395
Video+HSD+CDV	17,807	1,285	13	7	57	110	167	138	16,030
Total	54,728	4,387	2,530	10,819	1,553	13,173	638	3,916	17,712

My data structure consists of one table that contains monthly customer level data. Each record has the following indicators that represent the product(s) the customers have (HSI Service Flag, Voice Service Flag, Video Service Flag, No Service Flag). The Cohort Month field represents the month that the customer is being tracked (number from 1-12). The Churn Key field is the unique key, which is a concatenated field comprised of the Company Key & Fiscal Month fields.

Any suggestions on how I could go about producing this data set?

Thanks!

Maria

cross tab analysis help

pover — Fri, 21 Jan 2011 02:35:35 GMT

Maria,

What I would do is create an additional column called "Product Combination" and with an if() evaluated the Flag columns to put the values "No Service", "Video Only", "HSD Only", etc.

Then I would duplicate the table and link the original and the duplicated tables by the customer ID.

The dimensions of the table would be Month (orginal), Month_duplicate, Product_Combination (original) and Product_Combination_duplicate with a count(distinct Customer_ID) expression.

Good luck.

cross tab analysis help

Mon, 31 Jan 2011 22:45:02 GMT

Karl,

what I ended up doing, following along your suggestion, is creating 1 more field on the same table, that represents the product for month 7. So, I joined the 2 tables, got the product from month 7, appended that to the original table, and then droped the duplicate table.

It worked and I didn't have to have 2 duplicate tables, especially since the original table had 22 million records.

Thanks for your input