topic exlcude selection in count set statement in QlikView

exlcude selection in count set statement

karensmith — Fri, 18 Sep 2009 02:47:21 GMT

I am currently using this statement to count distinct distributors, but exclude users selection of distributor from the count.

count(distinct {< {*}-{$(=chr(39) & Distributor & chr(39))}>}Distributor)

User selects distributor name from listbox, and then chart sum values for the one distributor. And then I have other fields to sum all other distributors that are not selected.

I have several qty fields. POS Qty, POA Qty, Bklg Qty.

I sum the above 3 catergories like this (one example):

sum

({< Distributor = {*}-{$(=chr(39) & Distributor & chr(39))} , FYYear=,FYMonthName=,MonthSequenceNum={$(=only(MonthSequenceNum)-1)}>} POS_Qty) +

sum

({< Distributor = {*}-{$(=chr(39) & Distributor & chr(39))} , FYYear=,FYMonthName=,MonthSequenceNum={$(=only(MonthSequenceNum)-2)}>} POS_Qty)+

sum

({< Distributor = {*}-{$(=chr(39) & Distributor & chr(39))} , FYYear=,FYMonthName=,MonthSequenceNum={$(=only(MonthSequenceNum)-3)}>} POS_Qty)

I would like the count to refelct the other distributors that actually have values in the above catergories, but I'm getting strange results. Some times my count = 0 when there are values for the other distributors, and the count is also including the selected distirbutor.

I've also tried count(distinct{1} Distributor), but this gives me the count of all distributors and ignores any other selection. I know why this is happening because {1}.

Does anyone know how to do a count of distributors that are not selected?

exlcude selection in count set statement

tabletuner — Fri, 18 Sep 2009 04:00:38 GMT

Hi Karen,

I don't really understand your problem. Maybe you are able to further explain the problem from a less technical point of view. Besides that I would like to see the table structure you are using. And maybe you are able to post a qvw?

Regards,

Tjeerd

exlcude selection in count set statement

Anonymous — Fri, 18 Sep 2009 09:25:32 GMT

Hi Karen,

You should be able to use {1-$} to get the full count minus the selected count, i.e. the excluded count.

From the F1-help under the "Set Analysis" sheet in QV:

sum( {1-$} Sales )
returns sales for everything excluded by the current selection.

exlcude selection in count set statement

Fri, 18 Sep 2009 12:46:19 GMT

As johannes suggested {1-$} will do the trick for you.

And regarding {1}. It excludes all selections, and you will be analysing against a full set of data always.

exlcude selection in count set statement

karensmith — Fri, 18 Sep 2009 19:13:29 GMT

Thanks Guys. Let me see if I can better explain with the following examples. User makes year, month, distributor selection.

The first 8 columns represent the selected Distributors Inventory and Sales values. Field 'XX'= the count of all other distributors that are not selected, but have values for the same catergories. If the fields others are = 0 then xx = 0. In some cases this true, but in others the count = 1, which truly represents the selected distributor. The count should never include the selected distributor, but only the other distributors that actually have sales or inventory in the same time period.

exlcude selection in count set statement

karensmith — Fri, 18 Sep 2009 19:14:36 GMT

Thanks Johannes,

I tried your suggested and got the results that I posted in my previous response. Please see that response.