Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
acbishop2
Creator
Creator

Numbers Not Adding Up on Bar Graph

Hello All,

Yet again, I have run into a situation that simply does not make sense to me. Let's see if I can make this easy to understand...

I want to measure volume for specific periods of time. I have a bar chart set up with [Date.autoCalendar.Date] as the dimension and Count(If([Date] > 0, [Key])) as the measure. Here's an example of what happens:

QlikNumbersHelp.JPG

The Volume KPI has the correct number for volume, and has the same formula as the measure in the graph: Count(If([Date]>0, [Key])). The volume during this 7-day period is 273, but the graph shows 184, nearly 100 less than what it should be.

The issue is further complicated when I want to see volume by employee. I added [Employee] as a second dimension in the graph above and got this:

QlikNumbersHelp2.JPG

The volume as shown by the graph now adds up to 354.

So now I'm lost.

My only thought is that is must have to do with the way my data is set up. I started with one table and created 3 cross tables from it so that different fields could be bound together with a key. Still, I've used similar set-ups with other apps and never run into this problem.

All ideas are welcome. Thanks ahead of time.

-Aaron

1 Solution

Accepted Solutions
acbishop2
Creator
Creator
Author

I figured it out. Not all combinations of [Event]&[ID-Code] were unique, so there was some overlap that caused the totals to be messed up. To fix this, I threw in a [Row] field with just the row number in it, then made this part of the key: [Event]&[ID-Code]&[Row] AS [Key].

Everything else fell into place.

Thanks a ton for the help!

View solution in original post

13 Replies
krishna_2644
Specialist III
Specialist III

you have to use proper aggregations in the charts. sometimes the expression you use in KPI may not work the same in the tables/charts.this is because you are splitting up the data based on your dimensions.so the expressions in charts need proper aggregation.

Please post some sample data to look into this issue.

thanks

Digvijay_Singh

What do you get if you do this in KPI? -

Sum(Aggr(Count(If([Date]>0, [Key])),[Date.autoCalendar.Date]))

acbishop2
Creator
Creator
Author

The same: 273

Digvijay_Singh

But your bar graph values also totals to 273? Where do you see 184?

acbishop2
Creator
Creator
Author

Ok, wow. I swear I added it up like 5 times and it didn't match.

Disregard the first part of my post then. The second still stands. Do you know why the total is changing when the second dimension is added?

I'm working on uploading some sample data if you need that.

Digvijay_Singh

What do you see in KPI for this -

Sum(Aggr(Count(If([Date]>0, [Key])),[Date.autoCalendar.Date]),[Employee])

Digvijay_Singh

Ha ha, at least I solved your first problem.

acbishop2
Creator
Creator
Author

Here's some sample data. It's limited, so the numbers aren't the same, but I made sure that the structure was the same.

The same problem happens:

QlikNumbersHelp3.JPG

Attached are the .qvf and .xlsx files.

acbishop2
Creator
Creator
Author

That gives me an error: "Sum takes only 1 parameter."

If I move the second aggr() parenthesis to the end:

Sum(Aggr(Count(If([Date]>0, [Key])),[Date.autoCalendar.Date],[Employee]))

then I get 354.