topic Re: Set analysis zero values if no selection is made in QlikView

Set analysis zero values if no selection is made

Anonymous — Thu, 13 Oct 2011 20:00:25 GMT

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

Set analysis zero values if no selection is made

Anonymous — Thu, 13 Oct 2011 20:09:50 GMT

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.

Set analysis zero values if no selection is made

Thu, 13 Oct 2011 23:31:54 GMT

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

Set analysis zero values if no selection is made

Anonymous — Fri, 14 Oct 2011 12:28:53 GMT

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

Set analysis zero values if no selection is made

Fri, 14 Oct 2011 15:10:24 GMT

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...

Set analysis zero values if no selection is made

Anonymous — Fri, 14 Oct 2011 15:18:59 GMT

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.

Set analysis zero values if no selection is made

swuehl — Fri, 14 Oct 2011 15:28:23 GMT

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?

Set analysis zero values if no selection is made

Anonymous — Fri, 14 Oct 2011 15:36:23 GMT

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.

Set analysis zero values if no selection is made

Fri, 14 Oct 2011 15:39:36 GMT

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

Re: Set analysis zero values if no selection is made

Anonymous — Fri, 14 Oct 2011 15:48:25 GMT

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

Set analysis zero values if no selection is made

Fri, 14 Oct 2011 16:48:44 GMT

Count({1<view_date = {">= $(=vPivot_StartDate)"}, view_date = {"< $(=vPivot_Date)"}, [CAM USA or International] = {'USA'}, [PUB USA or International] = {'USA'}, [PUB_State] = {'CAMS_State'}>} DISTINCT pubid)

Give that a try for all customers. Can you upload your qlikview document? Or part of it? If I could see your data and where you're trying to show these values maybe I'd be more help.

Set analysis zero values if no selection is made

swuehl — Fri, 14 Oct 2011 17:02:21 GMT

Well, you could combine the set analysis with the if clause, use something like

=sum( {<view_date={">=$(=(vPivot_StartDate))<$(=date(vPiovot_Date))"),[PUB USA or International] = {USA},[CAM USA or International]={USA} >} if(CAMS_STATE=PUB_State, Usage,0))

Re: Set analysis zero values if no selection is made

Anonymous — Fri, 14 Oct 2011 17:51:18 GMT

Thank you both for your suggestions, but I'm still getting the same results.

The only way I could upload the qlikview document would be to remove the data, because of the information within the document. I'm not sure seeing the document without the data would help solve the issue though.

I truely appreciate the help, thank you!

Set analysis zero values if no selection is made

swuehl — Fri, 14 Oct 2011 17:57:19 GMT

Yould could scramble some fields, e.g. personal information, ( go to Document properties -> Scrambling), but I don't want to talk you into something.