Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
In that case, I would recommend using an if statement
Count(DISTINCT {<[Upsell ARR (converted)] = {">0"}>} If([Close Date] > [Campaign Start Date], [Opportunity ID]))
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])
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.
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
In that case, I would recommend using an if statement
Count(DISTINCT {<[Upsell ARR (converted)] = {">0"}>} If([Close Date] > [Campaign Start Date], [Opportunity ID]))
PERFECT. Amazing job again Sunny.