Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
The #1 reason QlikView customers adopt Qlik Sense is a desire for a modern BI experience. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

exlcude selection in count set statement

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?





5 Replies
Highlighted
Creator III
Creator III

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

Highlighted
Master
Master

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.

Highlighted
Not applicable

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.

Highlighted
Contributor II
Contributor II

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.

Highlighted
Contributor II
Contributor II

Thanks Johannes,

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