Help using Count function

[#1]

Hi guys,

I need to calculate the average of Parts that appear every month, (I should count also those parts that appears in more than 1 month)

In example:

Month PartNo

2010-01 Part1

2010-01 Part2

2010-01 Part3

2010-01 Part4 Total Count for 2010-01 => 4

2010-02 Part1

2010-02 Part10

2010-02 Part11

2010-02 Part12

2010-02 Part13

2010-02 Part14 Total Count for 2010-02 ==> 6

Total Count for 2010-01 + 2010-02 ==> 10

Average should be ==> 10/2 = 5

The problem is that using = Count( [Part No]) gives me 9, because Part1 appears twice.

How can I work it around?

Thanks in advance,

Aldo.

[#2 · Accepted solution]

hi aldo,

first concatenate your month and part no in your script itself somewhat like this

tab1:

load Month&','&PartNo as column;

load *inline[

Month PartNo

2010-01 Part1

2010-01 Part2

2010-01 Part3

2010-01 Part4

2010-02 Part1

2010-02 Part10

2010-02 Part11

2010-02 Part12

2010-02 Part13

2010-02 Part14

];

and then use

load count(mid(column,index(column,',')+1)) as mycount resident tab1;

thanks

[#3]

Hi Aldo!

How about using = Count( distinct [Part No]).

That should work..

-Lester

[#4]

Concatenating Month with PartNo was the solution I was looking for.

Thanks,

Aldo.

[#5]

Hi Lester,

I tried Count(Distinct...), but it didn't work.

Thanks,

Aldo.

[#6]

Qlikview use an associative model to store data , every occurence of each field is stored only one time , so when we use count ( partno ) , the bahavior is like count(distinct partno). try also to add a column with 1 as value for all rows and do a sum() to this field .

ex:

load month ,

partno ,

1 ;

hope that it will help

[#7]

Thanks for your explanation.

Aldo.