topic How to get counts on unselected items in QlikView

How to get counts on unselected items

Fri, 07 Jan 2011 12:46:08 GMT

Hello,

I'm hopeful that someone can find an answer to my problem:

Considering a table as follows:

Department	Item
A	w
A	x
A	y
A	z
B	x
B	y
B	z
C	y
C	z

I want to create a label with the following text in it:

Label: You are seeing information for [number of departments] department(s) and [number of items] item(s) of a possible [total existing items within the departments available].

My problem is, when a selection is made within my Qlikview model and the above table results in something like this:

Department	Item
A	x
B	y

Where [Number of departments] = 2; [Number of items] = 2; [Total existing items = 7]

And considering that Department or item values A,B,x,y haven't been selected in the model and are just the available results of other selections, how can I calculate the above numbers using set analysis? If possible I would like to avoid having to modify the load scripts.

Thank you for helping!

How to get counts on unselected items

amars — Fri, 07 Jan 2011 13:16:14 GMT

Hi,

You can write an expression like this

='Where [Number of departments] =' & Count(Distinct Department) & '; [Number of items] = ' & Count(Distinct Item) & '; [Total existing items = ' & (getselectedcount(Item) + getnotselectedcount(Item)) & ']'

How to get counts on unselected items

Fri, 07 Jan 2011 13:32:02 GMT

Thank you for your reply,

Count Distincts work fine, the problem comes when calculating the Total Existing items. Your suggestion returns 0 because no Items or Departments have been selected. Maybe those functions only work on actual selections in a field?

Daniel

How to get counts on unselected items

Fri, 07 Jan 2011 14:20:28 GMT

I might have found the answer. With this formula I seem to be getting the right result for the Total Existing Items:

=Count({1<Department=P({$}Department)>} Item)

Which means that, excluding the current selection, count the total items for the current possible Departments.

I'm not convinced that getting the right results isn't just a coincidence though. Would someone be able to comment on this?

Thanks