Set Analysis: use all the selections in list boxes apart from one

[tinkerz1]

Hi,

I am using list boxes to reduce a data set, but I want one list box selection to be ignored.

I am trying to not reduce the data set by any selection in:

[Logged by Group from Escalate To]

How do I do this?

Count({[MonthlyReport1]<[Date Reference Info]={'Logged Date'},[Logged by Group from Escalate To]=$::[Logged by Group from Escalate To]>}distinct ReferenceNumber)

Thanks

Neil.

[sunny_talwar]

Try this:

Count({[MonthlyReport1]<[Date Reference Info]={'Logged Date'},[Logged by Group from Escalate To]>} DISTINCT ReferenceNumber)

[tinkerz1]

Ok, thanks that worked, but if I wrap it in an if statement, and an if statement with getselectedcount it fails.

if([ID]=4,
Count({[MonthlyReport1]<[Date Reference Info]={'Logged Date'},[Logged by Group from Escalate To]>}distinct ReferenceNumber)
)

Or

if([ID]=4,
if(getselectedcount([Escalate To from Logged by Group])>0,Count({[MonthlyReport1]<[Date Reference Info]={'Logged Date'},[Logged by Group from Escalate To]>}distinct ReferenceNumber),'No data')
)

why would that be?

[sunny_talwar]

Not sure what your goal is. Can you elaborate a little maybe?

[tinkerz1]

OK,

I am making a sheet that is a data block, 1 row of data per comment/label and per pivot table.

The [ID] = 4 relates to the comment in the dimension (this pulls the label from the spreadsheet into Qlik)

Then I am using alternates states, just one in this sheet.

In 4 of the list boxes in this sheet I will make selections like, date, company, type etc, again all in the same alternate state.

Then in the same sheet in the same alternate state, I have 2 further list boxes, the pivot table expression will either ignore any selection in both list boxes, or use 1 of the selections and ignore the other to make a full set of data to calc the expression on.

Hope this makes sense, I am using Qlik  v11.00.11414.0 SR2.

Thanks

[bgerchikov]

Hi Neil,

your resultset may have a problem because in Count you use alternate state [MonthlyReport1], while GetSelectedCount function refers to base state.

Try this:

if(getselectedcount([Escalate To from Logged by Group],0,'MonthlyReport1')>0,Count({[MonthlyReport1]<[Date Reference Info]={'Logged Date'},[Logged by Group from Escalate To]>}distinct ReferenceNumber),'No data')

[tinkerz1]

Hi Boris,

I don't have the 11.2 version of qlik in this firm

Thanks

[bgerchikov]

In this case, you can use the following statement instead of Getselected count:

If(Count({1}[Escalate To from Logged by Group]) <> Count({$}[Escalate To from Logged by Group]).....

[bgerchikov]

Correction

If(Count({1}[Escalate To from Logged by Group]) <> Count({MonthlyReport1}[Escalate To from Logged by Group]).....

[tinkerz1]

Ok thanks but that always triggers as my Alternate state called monthlyreport1 is reduced by the other selections in the alternate state like company, date etc.