set analysis inner select of a group

[samuel_lin]

Hi Qlikers,

I am thinking this is impossible, but just want to be sure.

I have a table in Qlik like this:

MarketingChannels	Jan16	Feb16	Mar16	Apr16
Channel1	18	310	786	810
Channel2	84	640	1,229	1,040
Channel3	5	20	35	0

for first measurement column, the formula is:

count({<[FirstOrderDate]={'>=01/01/2016<=01/31/2016'}, [order date]={'>=01/01/2016<=01/31/2016'}>} distinct MasterContactID)

and got: a count of 18

What I hope to achieve is to isolate these 18 people, and track their revenue from the month they joined 01/01/2016 to present, any idea how this can be done?

say in the data structure there exists: "Revenue" column associated with [order date]

Many thanks,

Samuel

[sunny_talwar]

I don't see FirstOrderDate and Order Date columns? Where is this information?

[samuel_lin]

hi Sunny,

Thank you for your reply.

There are two tables

1. one has the transaction data:

[MasterContactID]

[order date]

[revenue]

[marketingChannel]...

2. the other is contacts table:

[MasterContactID]

[firstOrderDate]

(I should also be able to use the transaction table to figure out firstOrderDate)

...

the first column: Jan16 is calculated by this formula:

count({<[FirstOrderDate]={'>=01/01/2016<=01/31/2016'}, [order date]={'>=01/01/2016<=01/31/2016'}>} distinct MasterContactID)

and I would like to track these contacts of any further revenue since they joined

does this make sense?

[sunny_talwar]

Nope, not completely .

[samuel_lin]

Hi Sunny,

this is a straight table with dimension: MarketingChannel

each column follows is just the measurement. This is not a pivot table. Does this make sense now?

[#6]

I think it would be easier to create a [First Order Month] and an [Order Month] field in the load script rather than trying to use a set expression.  This will do a lot of the heavy lifting for you and then allow you to create a chart that shows all revenue for all people in all channels and then use filters to drill into the data.  In your case you would just have to select Channel1 from the MarketingChannel field and  Jan16 from the First Order Month field and you would see the revenue trending for your 18 people.