Evaluate Date Per Row

[Anonymous]

The set analysis below successfully calculates UPSELL ARR ($) closed AFTER a campaign date in Salesforce. There is one slight issue - This only works when I have ONE CAMPAIGN selected. When I have multiple campaigns selected, it calculates based on the aggregate campaign start date and I get inaccurate measures.

count({<[Close Date]={"= [Close Date] > [Campaign Start Date]"}, [Upsell ARR (converted)] = {">0"}>}distinct([Opportunity ID]))

GOAL: I want to be able to evaluate this expression accurately with several campaigns chosen, so I need this expression to compare ALL close dates vs the specific campaign start date on each row.

Hopefully this makes sense. Thank you in advance for your help.

- dave

[sunny_talwar]

I think you will need to use aggr function in that case. Set Analysis will only work if you can create a new field in the script. Something like this

[Close Date] & CAMPAIGN as NewField

and then may be this

Count({<NewField={"= [Close Date] > [Campaign Start Date]"}, [Upsell ARR (converted)] = {">0"}>} DISTINCT [Opportunity ID])

Or try this before making any change

Count({<[Opportunity ID] = {"=[Close Date] > [Campaign Start Date]"}, [Upsell ARR (converted)] = {">0"}>} DISTINCT [Opportunity ID])

[Anonymous]

Thank you for the help Sunny

Count({<[Opportunity ID] = {"=[Close Date] > [Campaign Start Date]"}, [Upsell ARR (converted)] = {">0"}>} DISTINCT [Opportunity ID])

Did not work immediately, so it looks like I will need to create new field as suggested. I will revert with the findings.

[Anonymous]

Sunny - There is an issue here. The field CAMPAIGN and CLOSE DATE are in two different tables, so concatenating them is impossible, i think? I have posted a picture of the data model below

[sunny_talwar · Accepted solution]

In that case, I would recommend using an if statement

Count(DISTINCT {<[Upsell ARR (converted)] = {">0"}>} If([Close Date] > [Campaign Start Date], [Opportunity ID]))

[Anonymous]

PERFECT. Amazing job again Sunny.