topic can't get the sum using count in QlikView

can't get the sum using count

Anonymous — Wed, 15 Jun 2011 14:04:13 GMT

Good afternoon,

I kindly ask some help for the following problem.

I have a chart straight table with one dimension which is the month and several expressions.

As you can see in the below picture the value in the last column, named "count", is not the total of the four columns 2008, 2009, 2010 and 2011:

I am sorry I can't manage to attached a small qvw file. I tried but I could not reproduce the problem.

When I tried on a different example it seemed to me that the last column will count only the distinct [Serial Number].

Your help is appreciated.

Thanks

Antonio

can't get the sum using count

pat_agen — Wed, 15 Jun 2011 15:52:28 GMT

hi Antonio,

if you really want to build your chart as yiou have shown, that is "hardcoding" each expression than you may be able achieve your result by using this in your expression for total:

count({$<Year={'2008'}> [Serial Number]) + count({$<Year={'2009'}> [Serial Number])

+ count({$<Year={'2010'}> [Serial Number]) + count({$<Year={'2011'}> [Serial Number])

but why don't you just create a pivot table with month and year as dimensions and count([Serial Number]) as the expression? Will this not do the trick?

can't get the sum using count

Wed, 15 Jun 2011 16:13:01 GMT

Hello Antonio,

I think the problem is that you may have the same [Serial Number] in different years. So when you use the Count in the last column, it will count the Total Serial numbers in the last 4 years.

It'd be easier if you use a pivot table, with 2 dimensions: Month (as the left dimension) and Year. Then use the expression Count([Serial Number]). Finally go to the Presentation tab and check Show Partial Sums option.

Regards,

Thu, 16 Jun 2011 06:26:47 GMT

Well I wanted to respond by saying I'm surprised it doesnt work for you, but think you managed to get it working now?

can't get the sum using count

Thu, 16 Jun 2011 06:27:36 GMT

np, glad it helped you.

can't get the sum using count

Anonymous — Thu, 16 Jun 2011 06:46:23 GMT

Jeroen,

Since I could not post a .qvw it took me sometime to have it working, I made my first comment when I tried

Sum( Aggr( Count( [Serial Number] ), Year ) )

that did not work in my .qvw file

then I needed to adjust it to

Sum( Aggr( Count( [Serial Number] ), Month ) )

in order to have it working fine.

From your suggestion I learnt how to use

SUM AGGR COUNT

and ,once more, how important is to try to post a .qvw file. But this time I could not manage to reduce it to a file that could be published.

Thanks

Antonio

Re: can't get the sum using count

Thu, 16 Jun 2011 06:53:48 GMT

Well actually, I'm still working with the personal edition, eagerly awaiting license keys. So what I do for my questions is create a tiny example with an inline dataset.

Happy to hear you understand how it works! Good luck.