Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Evaluate Date Per Row

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

1 Solution

Accepted Solutions
sunny_talwar

In that case, I would recommend using an if statement

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

View solution in original post

5 Replies
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
Not applicable
Author

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
Not applicable
Author

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

Capture22.PNG

sunny_talwar

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
Not applicable
Author

PERFECT. Amazing job again Sunny.