Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

apprentice
Not applicable

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:

Untitled-1.jpg

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

Tags (1)
1 Solution

Accepted Solutions
Not applicable

can't get the sum using count

You would have to sum the totals for each year:

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

11 Replies
pat_agen
Not applicable

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?

Not applicable

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

Not applicable

can't get the sum using count

You would have to sum the totals for each year:

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

apprentice
Not applicable

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

apprentice
Not applicable

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

apprentice
Not applicable

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

apprentice
Not applicable

can't get the sum using count

jbpvuurens,

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

Thanks for helping.

Antonio

Not applicable

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?

Not applicable

can't get the sum using count

np, glad it helped you.