can't get the sum using count

[Anonymous]

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

[pat_agen]

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?

[#3]

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

[#4 · Accepted solution]

You would have to sum the totals for each year:

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

jbpvuurens,

I adjusted my example and made more test ausing your suggestion and and it works !

Thanks for helping.

Antonio

[#9]

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

[#10]

np, glad it helped you.