topic Re: count if in QlikView

count if

avishaiz — Wed, 25 Nov 2020 16:16:04 GMT

i want to count the number of rows where there is a difference between the sum of counted items (the numbers that we have in our data system) and the actual number of items on the shelf

how can i do that?

Re: count if

sunny_talwar — Sun, 28 Aug 2016 15:25:52 GMT

Would you be able to provide a sample to have a look at your requirement with an expected output?

Re: count if

avishaiz — Mon, 29 Aug 2016 06:40:28 GMT

here is a sample data

Re: count if

sunny_talwar — Mon, 29 Aug 2016 09:55:08 GMT

Would you also be able to share some information about the expected output?

Re: count if

avishaiz — Mon, 29 Aug 2016 10:17:29 GMT

i am subtracting the number of Inventonhand from the counted to see if there are any missing items

now i want to show the number of rows where there was a difference

eg:

counted - inventonhand = -1, that means there is one item missing

the result shuld be

number of items counted - 25 rows

number of rows with missing items - 3

number of tows with extra items - 2

from here i think it will be easy to make a table/graph or a pie chart to show results

Re: count if

varshavig12 — Mon, 29 Aug 2016 10:27:29 GMT

I'm not sure,

But, do you want something like:

count(Column_F) where, Column_F is not equal to 0 ?

Re: count if

ToniKautto — Mon, 29 Aug 2016 10:33:07 GMT

I would use a Count( If( ) ) construction in a chart, and embed it a Aggr() for total aggregation. Please see attached QVW for example.

Count(If(System<>Shelf,1)

Sum(Aggr(Count(If(System<>Shelf,1)) , Product ))

Re: count if

effinty2112 — Mon, 29 Aug 2016 10:49:57 GMT

Hi Avishai,

I think I've got a solution but when I paste it here the order of parts of the expression become jumbled. Very odd!

This is the expression in a textbox properties sheet.

When I copy and paste the expression here it comes out like:

=count(if([כמות בנמצא] <>[כמות בספירה],[מס' מק"ט]))

Cheers

Andrew

Re: count if

avishaiz — Mon, 29 Aug 2016 11:11:25 GMT

hi actually it comes out good in the copy and in the script editor its just that qlik is not good with hebrew

Re: count if

avishaiz — Mon, 29 Aug 2016 11:22:35 GMT

what does the aggr do?

i get the same result with

Count(If(System<>Shelf, Product ))

Re: count if

avishaiz — Mon, 29 Aug 2016 11:23:41 GMT

i need to be able to separate between items that are missing and items that are in excess

so i think this wont be good

Re: count if

avishaiz — Mon, 29 Aug 2016 12:45:34 GMT

i tried the two methods you recommended

now the thing is i got the same items on different shelves

so with an item that was counted twice in two different shelves i get a count of two items but i need only one since its the same item.

how can i solve this ?