Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Advanced set analysis - sales since last meeting date

Hello

Suppose I have a customer table, containing two fields: [Customer ID] and [Date of last meeting]. There is only one [Date of last meeting] per customer.

A second table contains sales transactions, including: [Transaction ID] , [Customer ID] , [Date of Sale] and [Value of Sale].

I need to produce a chart that shows, for each customer, the value of sales since the last time that customer was met.

I can't quite get set analysis to work. It must be something like the below, but this doesn't work.

sum ({1< [Customer ID] = P([Customer ID]) , [Date of Sale] >= [Date of last meeting] >} [Value of Sale] )

Can this be done, and if so, how do I need to change the formula?

Many thanks

James

1 Solution

Accepted Solutions
MVP
MVP

Re: Advanced set analysis - sales since last meeting date

Set analysis is evaluated once per chart, so it won't look at the dimension values (that's what you probably intend by using Date of sale >= Date of last meeting).

So I don't think set analysis is the way to go here.

You should be able to use a conditional embedded in the sum to check if the date of sales is after the date of last meeting:

=sum(if([Date of Sale] >= [Date of last meeting], [Value of Sale]))

Another approach would be to use an IntervalMatch between your two tables (creating a second field for the interval end in your customer table with date of today). Check the Intervalmatch Load prefix if you are interested in that.

Hope this helps,

Stefan

2 Replies
MVP
MVP

Re: Advanced set analysis - sales since last meeting date

Set analysis is evaluated once per chart, so it won't look at the dimension values (that's what you probably intend by using Date of sale >= Date of last meeting).

So I don't think set analysis is the way to go here.

You should be able to use a conditional embedded in the sum to check if the date of sales is after the date of last meeting:

=sum(if([Date of Sale] >= [Date of last meeting], [Value of Sale]))

Another approach would be to use an IntervalMatch between your two tables (creating a second field for the interval end in your customer table with date of today). Check the Intervalmatch Load prefix if you are interested in that.

Hope this helps,

Stefan

Not applicable

Re: Advanced set analysis - sales since last meeting date

Thanks Stefan. Worked exactly how I wanted it.

James

Community Browser