5 Replies Latest reply: Jan 27, 2017 1:32 PM by David Rudolph

# set analysis inner select of a group

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

• ###### Re: set analysis inner select of a group

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

• ###### Re: set analysis inner select of a group

hi Sunny,

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?

• ###### Re: set analysis inner select of a group

Nope, not completely .

• ###### Re: set analysis inner select of a group

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?

• ###### Re: set analysis inner select of a group

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.