topic Re: Evaluate Date Per Row in QlikView

Evaluate Date Per Row

Anonymous — Wed, 01 Mar 2017 16:41:12 GMT

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

Re: Evaluate Date Per Row

sunny_talwar — Wed, 01 Mar 2017 16:45:57 GMT

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])

Re: Evaluate Date Per Row

Anonymous — Wed, 01 Mar 2017 16:52:45 GMT

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.

Re: Evaluate Date Per Row

Anonymous — Mon, 06 Mar 2017 14:12:51 GMT

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

Re: Evaluate Date Per Row

sunny_talwar — Mon, 06 Mar 2017 14:15:34 GMT

In that case, I would recommend using an if statement

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

Re: Evaluate Date Per Row

Anonymous — Mon, 06 Mar 2017 15:40:13 GMT

PERFECT. Amazing job again Sunny.