11 Replies Latest reply: Jun 16, 2011 2:54 AM by Jeroen Vuurens

# can't get the sum using count

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].

Thanks

Antonio

• ###### can't get the sum using count

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

Pat,

thanks for your suggestion, although it does not seem to work in my example. Unfortunately I can't post a .qvw example. It would be much easier.

Thank

Antonio

• ###### can't get the sum using count

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,

Steve

• ###### can't get the sum using count

scarrasco,

thanks for your suggestion. I tried but does not seem to work. You are right, that's seems to be the problem: it counts only the distinct serial number.  I am sorry I can't manage to post a .qvw example.

Thank

Antonio

• ###### can't get the sum using count

You would have to sum the totals for each year:

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

• ###### can't get the sum using count

jbpvuurens,

thanks for your suggestion, but does not seem to work in my example. As I wrote to Pat I am sorry I can't manage to post a .qvw example.

Thank

Antonio

• ###### can't get the sum using count

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

jbpvuurens,

Thanks for helping.

Antonio

• ###### can't get the sum using count

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

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.