How do I use set analysis concept within a chart expression

Thu, 11 Apr 2013 05:40:59 GMT

Hi -

I'm trying to create a chart that will show the number of "First Time Customers" by Fiscal Quarter (or FTCInterval .. .aka First Time Customer Interval).

We are defining "First Time Customer" as a customer who has not purchased within the past 4 quarters.

So ... I am able to use the load script below to load data -

And then use the following expression using set analysis to display the information that I would expect in a "Text Object":

=count ({$<Customer = p({$<[Fiscal Quarter]={20131}>}Customer) - p({$<FTCInterval={20131}>} Customer)>} distinct Customer)

//Count of customers that bought in Fiscal Quarter 20131 but NOT in FTCInterval 20131 (between 20121 and 20124)

In the example with the data in the load script, this would return the value "3" - because "Customer New1", "Customer New2", and "Customer New3" did not have any sales in the

However - I can't figure out to get this represented in a chart where "Quarter" is the dimension and the count of "First Time Customers" is the expression.

Can anyone provide guidance on options for doing this?

Here is an example of the load script that I am using:

FTCInterval:

LOAD * INLINE [

FTBInterval, From Quarter, To Quarter, FTBIntervalOrder

20111, 20101, 20104, 1

20112, 20102, 20111, 2

20113, 20103, 20112, 3

20114, 20104, 20113, 4

20121, 20111, 20114, 5

20122, 20112, 20121, 6

20123, 20113, 20122, 7

20124, 20114, 20123, 8

20131, 20121, 20124, 9

20132, 20122, 20131, 10

];

sales_transaction:

LOAD * INLINE [

Fiscal Quarter, Customer, Product, Sales

20121, CustomerA, ProductXXX, 300.00

20121, CustomerB, ProductZZZ, 200.00

20122, CustomerB, ProductXXX, 300.00

20123, CustomerA, ProductYYY, 100.00

20124, CustomerC, ProductYYY, 100.00

20124, CustomerD, ProductZZZ, 100.00

20131, CustomerA, ProductXXX, 100.00

20131, CustomerB, ProductXXX, 100.00

20131, CustomerNew1, Product123, 1000.00

20131, CustomerNew2, Product345, 100.00

20131, CustomerNew3, Product341, 50.00

];

FTCIntervalMatch:

IntervalMatch([Fiscal Quarter])

LOAD [From Quarter], [To Quarter]

resident FTCInterval;

The sales transaction data being loaded looks similar to below:

Fiscal Quarter	Customer	Product	Sales
20121	Customer A	Product XXX	300.00
20121	Customer B	Product ZZZ	200.00
20122	Customer B	Product XXX	300.00
20123	Customer A	Product YYY	100.00
20124	Customer C	Product YYY	100.00
20124	Customer D	Product ZZZ	100.00
20131	Customer A	Product XXX	100.00
20131	Customer B	Product XXX	100.00
20131	Customer New1	Product 123	1000.00
20131	Customer New2	Product 345	100.00
20131	Customer New3	Product 341	50.00

Thank you for any help/guidance you can provide!

Michelle

topic How do I use set analysis concept within a chart expression in QlikView

How do I use set analysis concept within a chart expression