topic Dynamic dimension with set analysis in QlikView

Dynamic dimension with set analysis

Mon, 14 May 2012 16:04:19 GMT

Hi All,

I'm trying to put together a dynamic dimension with included set analysis.

I have a transaction style table that I need to bucket customers up for analysis over given time periods.

As a basic start I want to first count people who did Action_1 and not Action_2 ever.

So I try the following:

=IF(

AGGR(COUNT({<act_name={'Action_1'}>} act_name), unique_ref) > 0

AND

AGGR(COUNT({<act_name={'Action_2'}>} act_name), unique_ref) = 0, 'Action1 & Action2', 'Other')

This doesn't work. I can understand why, because by using set analysis in the second aggregation I believe it is impossible to have a count of 0 as I have implicitly filtered for rows that actually ARE action 2.

So I'm stumped as to my next move. I'm trying to avoid creating a table or flags, I want it to be fully dynamic.

Any ideas?

Thanks in advance!

GPC'

Re: Dynamic dimension with set analysis

IAMDV — Mon, 14 May 2012 16:59:34 GMT

GPC - Please can you post some sample data? It will help us to work with some data instead of trying just with the expression.

Thanks - DV

Dynamic dimension with set analysis

Tue, 15 May 2012 08:01:22 GMT

Hi DV,

So the data is transactional in that each row is an action:

ID, Action, Amount

1, Action_1, 40.00

1, Action_2, 60.00

1, Action_1, 12.00

2, Action_1, 5.00

2, Action_1, 3.00

So you can see that to test in set whether there is Action 1 and Action 2 will not work (i think) because they're on separate rows.

thanks

GPC

Re: Dynamic dimension with set analysis

IAMDV — Tue, 15 May 2012 11:01:30 GMT

Thanks GPC.

Please can you tell me what is the output you are looking? Please can you explain what you want to see a Dimension and Expression? Sorry, I haven't fully understood.

Thanks - DV

Dynamic dimension with set analysis

nstefaniuk — Tue, 15 May 2012 11:14:11 GMT

=IF(

AGGR(COUNT({<Action={'Action_1'}>} Action), ID) > 0

AND

isnull(AGGR(COUNT({<Action={'Action_2'}>} Action), ID)) = -1 , 'Action1 & Action2', 'Other')