exlcude selection in count set statement

[karensmith]

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?

[tabletuner]

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

[Anonymous]

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.

[#4]

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.

[karensmith]

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.

[karensmith]

Thanks Johannes,

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