Chart Dimension Date for Set Analysis Comparison

[beth_dalton]

Hello!

I have a table chart with [Campaign Name] as the dimension.  Each Campaign has one and only one start date, [Campaign Start Date].  For any campaign, there are multiple Opportunities that have a unique ID, [Opportunity ID] and a created date, [Oppty Create Date].  I need to count the number of opportunities that are not closed, and were created between the campaign start date and the campaign start date plus a number of days from a slider object (stored in the variable v_DayRange).

The expression below works if I have a particular campaign selected (as min([Campaign Start Date]) is the only start date for any given campaign).  It does not work otherwise, as it is using the minimum campaign start date for ALL of the campaigns to do the comparison to the opportunity create date in the set analysis.

=count({<[Opportunity Closed] = {'false'},[Oppty Create Date]={">=$(=min([Campaign Start Date])) <=$(=date(min([Campaign Start Date])+$(v_DayRange)))"}>}DISTINCT [Opportunity ID])

I basically need to aggregate the start date across the dimension [Campaign Name], but have been unsuccessful.  I have tried the Aggr() and Only() functions within the date comparison piece of the set analysis, as well as out.  Based on my limited understanding of these functions, I fell like they SHOULD work, so I'm just not sure if I am using them incorrectly or if you just can't aggregate across a dimension within set analysis.  I found a similar question to mine here: How to get minimum date for each month dimension using set analysis but isn't hasn't been answered.

Any suggestions wold be most appreciated - thank you!

[Anonymous]

Beth,

This is how set analysis work - it analyses set of data regardless chart dimensions.  You have to use good old if() function in your case.  Can keep the [Opportunity Closed] condition in the set of course, because it is independent from the Campaign.

Regards,

Michael

[ramoncova06]

I would create a flag for my starting camping date at script level and then use that as part of my set analysis filter

they are different ways to get the min date for the different campaigns, but that creates an array of data which QV has issues working with since it is expecting a fixed value

[beth_dalton]

Hi Michael,

Where would I use an If statement?  In the expression?  I'm not seeing how this would work.

Thanks for the reply!

Beth

[Anonymous · Accepted solution]

I think this will work:

=count({<[Opportunity Closed]={'false'} DISTINCT if([Oppty Create Date]>=[Campaign Start Date] AND [Oppty Create Date] <= ([Campaign Start Date] + v_DayRange), [Opportunity ID]))

[beth_dalton]

Hurrah, this worked (other than the missing >} to close the set analyis)!

=count({<[Opportunity Closed]={'false'} >} DISTINCT if([Oppty Create Date]>=[Campaign Start Date] AND [Oppty Create Date] <= ([Campaign Start Date] + v_DayRange), [Opportunity ID]))

Thank you so much!