Dynamic dimension with set analysis

[#1]

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'

[IAMDV]

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

[#3]

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

[IAMDV]

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

[nstefaniuk]

=IF(

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

AND

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