Flag based on value on multiple rows

[Anonymous]

Hi,

I have a data set that contains a unique value for each customer as well as a column which indicates what offer they received as well as the method of communication (channel).  For example;

ID	Offer	Received Letter	Received Email	Received Phone Call
1	20% off	Y	N	N
1	20% off	N	N	Y
2	15% off	N	Y	N
2	15% off	N	N	Y
2	20% off	Y	N	N
3	15% off	Y	Y	N
3	20% off	N	Y	N
3	50% off	Y	N	Y

What I need to do is create a column/columns which provide me with the channel combinations.  For example, in the above, customer number 1 has received a letter and a phone call for the same offer, so ideally i would liek to be able to have a flag that indicates that they have received this combination (letter & phone call).  The most important thing though is that this would need to be based on the same offer, as some customers may receive a letter and a call for different offers.

Ideally I'd love it to look like this

					Based on ID & Offer
ID	Offer	Received Letter	Received Email	Received Phone Call	Letter Only	Email Only	Phone Call Only	Letter & Phone Call	Letter & Email	Email & Phone Call
1	20% off	Y	N	N	N	N	N	Y	N	N
1	20% off	N	N	Y	N	N	N	Y	N	N
2	15% off	N	Y	N	N	N	N	N	N	Y
2	15% off	N	N	Y	N	N	N	N	N	Y
2	20% off	Y	N	N	Y	N	N	N	N	N
3	15% off	Y	Y	N	N	N	N	N	Y	N
3	20% off	N	Y	N	N	Y	N	N	N	N
3	50% off	Y	N	Y	N	N	N	Y	N	N

[Greg_Deckler]

What if it looked like this for ID 1?

					Based on ID & Offer
ID	Offer	Received Letter	Received Email	Received Phone Call	Letter Only	Email Only	Phone Call Only	Letter & Phone Call	Letter & Email	Email & Phone Call
1	20% off	Y	N	Y	N	N	N	Y	N	N

[Anonymous]

Hi,

that would be perfect. Any suggestions would be much appreciated.

Thanks

[Greg_Deckler]

You should be able to create measures like this:

Email Only = 
VAR tmpTable = SUMMARIZE(Table8,[ID],[Offer],"RL",MAX([Received Letter]),"RE",MAX([Received Email]),"RP",MAX([Received Phone Call]))
VAR RL = MAXX(tmpTable,[RL])
VAR RE = MAXX(tmpTable,[RE])
VAR RP = MAXX(tmpTable,[RP])
RETURN IF(RL="Y" && RE="N" && RP="N","Y","N")

[Anonymous]

Thanks very much for this Greg!  I have one quick question regarding the last piece

RETURN IF(RL="Y" && RE="N" && RP="N","Y","N") 

Does there always have to be 3 outcomes at the end (N,Y,N)?  Or can this simply be N?

[Greg_Deckler]

Not sure I understand, there are only two outcomes, Y or N:

RETURN IF(RL="Y" && RE="N" && RP="N","Y","N") 

The red part is the logic clause. Essentially for this case that is the only scenario where you would return a Y, all other cases are N.

[Anonymous]

My apologies.  This makes a lot of sense.  I just got mixed up in all the Ys & Ns.

Is is possible to use this as a column or is it for a measure only?

Many thanks.

[Greg_Deckler]

You could use it as a column if you did something along these lines (below). I really think it works better as a measure though because you can just create a table visualization with [ID], [Offer], Last of Received Letter, Last of Received Email, Last of Received Phone Call and then your measures.

Email Only = 
VAR tmpTable8 = FILTER(ALL(Table8),[ID]=EARLIER([ID]))
VAR tmpTable = SUMMARIZE(tmpTable8,[ID],[Offer],"RL",MAX([Received Letter]),"RE",MAX([Received Email]),"RP",MAX([Received Phone Call]))
VAR RL = MAXX(tmpTable,[RL])
VAR RE = MAXX(tmpTable,[RE])
VAR RP = MAXX(tmpTable,[RP])
RETURN IF(RL="Y" && RE="N" && RP="N","Y","N")

[Anonymous]

Hi Greg, I feel like this is very close, so thank you very much. 

However, when I use as a column i get the following for one of our customers.  It's showing as email only when he's had a letter (DM) and email (EM).  Any ideas?

[Greg_Deckler]

Sorry, that should have been:

Email Only Column = 
VAR tmpTable8 = FILTER(ALL(Table8),[ID]=EARLIER([ID])&&[Offer]=EARLIER([Offer]))
VAR tmpTable = SUMMARIZE(tmpTable8,[ID],[Offer],"RL",MAX([Received Letter]),"RE",MAX([Received Email]),"RP",MAX([Received Phone Call]))
VAR RL = MAXX(tmpTable,[RL])
VAR RE = MAXX(tmpTable,[RE])
VAR RP = MAXX(tmpTable,[RP])
RETURN IF(RL="N" && RE="Y" && RP="N","Y","N")

Note the first VAR statement change.

[Anonymous]

Thanks Greg,

Have applied the changes. but still getting the same result 😞

[Greg_Deckler]

Not sure, see attached. You want Table 8.

[Anonymous]

What happens in your example if you duplicate your first row, but add a Y to the letter or phone call?  do you get the same answer in your calculated column?

Many thanks

[Vvelarde]

@Anonymous

Hi, You can try doing this in Query Editor.

1. Group By ID & Offer

And Aggregation MAX of every Column (Letter, Email and PhoneCall)

2. Create custom Columns using "and " operator to evaluate the conditions.

Regards

Victor

[Greg_Deckler · Accepted solution]

Like this? See last row, a duplicate of the first row with a Y added to Email

[Anonymous]

Hi Greg,

Yours looks exactly how I want it. and even though i've replicated what you've got, I can't get the right outcome.  This is very strange!

Many thanks

[Greg_Deckler]

You have 2 OB's in your IF statement. One of them needs to be DM. Also, I would use ALL and not ALLSELECTED. Finally, ou have another column which you might need to filter on but I wouldn't worry about that until the other 2 issues are addressed.

[Anonymous]

Greg, you're a star.  Everything works perfectly now (I've also added the extra column to filter by).

Appreciate your help!!