Set analysis zero values if no selection is made

[Anonymous]

I'm trying to get a count of all publications, that a customer in the USA has access to, which are located in the customers state, base on a date range. If a customer is selected, then I get the results I need.  If a customer is not selected, then the result is zero.    Count({=$(=(vPivot_StartDate))<$(=date(vPivot_Date))"},[PUB USA or International]={USA},[CAM USA or International]={USA},[CAMS_State]={"=PUB_State=CAMS_State"}> } DISTINCT(pubid)) I would like to be able to see both a result for all USA customers that use in-state publications as well as the individual customer results as well.  Thanks, Jen

[Anonymous]

Count({=$(=(vPivot_StartDate))<$(=date(vPivot_Date))"},[PUB USA or International]={USA},[CAM USA or International]={USA},[PUB_State]={"=PUB_State=CAMS_State"}> } DISTINCT(pubid)) I left out the beginning of the statement.

[#3]

It looks like your syntax may be off in your statement.  Do you have an example you could upload?

[Anonymous]

This is the full expression:

count({<view_date={">=$(=(vPivot_StartDate))<$(=date(vPiovot_Date))"),[PUB USA or International] = {USA},[CAM USA or International]={USA}, [PUB_State]={"=PUB_State=CAMS_State"}>} Distinct(pubid))

[#5]

count({<view_date={">=$(=(vPivot_StartDate))","<$(=date(vPiovot_Date))"},[PUB USA or International] = {'USA'},[CAM USA or International] = {'USA'}, [PUB_State]={'PUB_State','CAMS_State'}>} Distinct pubid)

Give that a try...

[Anonymous]

Thank you very much for your response, but that resulted in zeros even with a customer selected.  I'm thinking I'm missing a $ somewhere because if one customer is selected it works.  If I want to see the overall view of all customers the result is zero.

[swuehl]

Hi Jen,

I think your problem is

[PUB_State]={"=PUB_State=CAMS_State"}

if you select more than one customer, this (PUB_State=CAMS_State will be ambigouus, I think.

I don't have a solution right now, maybe an indirect set expression? Or try with a row based conditional expression instead using advanced aggregation?

[Anonymous]

I think you're right.  Initial, I had this calculated with an IF statement, which worked, but caused memory issues. 

Thank you for taking the time to respond.

[#9]

Do you have a sample I could take a look at? 

[Anonymous]

This is the IF statement that works, but causes the memory issue.

=Sum(If(view_date >= vPivot_StartDate,if(view_date<vPivot_Date,if([CAM USA or International]='USA', if(PUB_National_Package='No', if(CAMS_STATE=PUB_State, Usage))))))