How to distinguish campaign waves

[Nora_Hor]

Hi! I am working with media campaign data in monthly breakdown. How can I define a certain campaign that was active for few consequtive months without a break? 

Example: Campaign A was onair in Jan-March, than 2 months off, than on again June-July. I want to distinguish these 2 campaigns from each other and than aggregate their data rows with group_by function.

Thanks, Nora

[julioms23]

new column with an if statement??

---

@Nora_Hor wrote:

Hi! I am working with media campaign data in monthly breakdown. How can I define a certain campaign that was active for few consequtive months without a break? 

Example: Campaign A was onair in Jan-March, than 2 months off, than on again June-July. I want to distinguish these 2 campaigns from each other and than aggregate their data rows with group_by function.

Thanks, Nora

---

[Nora_Hor]

Could you give me further help writing the statement? I can't define a statement as i don't know how to refer to previous row/cell. My data set looks like:

Campaign name	Year	Month	Value
A	2018	2	200
A	2018	3	415
A	2018	4	404
A	2018	8	30
A	2018	9	660
B	2018	4	440
B	2018	5	632
B	2018	10	30

Thank You very much!

[julioms23]

Better with Group By => campaing name =>sum => value.

[Nora_Hor]

Thanks but that's the point: A campaign had 2 separate waves that I need to keep separate during sum. That's my problem 🙂 

[dax]

Hi Nore_Hor,

I am not clear about your requirement, so if possible, could you please inform me more detailed information(such as your sample data and expected output)?

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Nora_Hor]

Hi,

I would aggregate data rows by campaign waves ( one wave = same campaign name, active for consequtive months without a break) So I need a statement or other kind of  solution to distinguish different campaign waves. 

Sample data:

Campaign name	Year	Month	Value
A	2018	2	200
A	2018	3	415
A	2018	4	404
A	2018	8	30
A	2018	9	660
B	2018	4	440
B	2018	5	632
B	2018	10	30

Expected output:

Campaign name	Year	Start Month	End Month	Value
A	2018	2	4	1019
A	2018	8	9	690
B	2018	4	5	1072
B	2018	10	10	30

Could you please help?

Thanks,

Nora

[julioms23]

I don't know how to do it...