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: 
stephenhasson
Creator
Creator

Confused on 'sum' function

Hi,

I cannot figure out why one bar graph on a sheet work perfecty and another, looking at the same field, 'doubles up'.....  I show in attached.

I have verified that 3116 hrs of labour is accurate. In the top summary graph it used 'Sum(EstProdHours)' but gives inflated figured. If I change only use EstProdHours then I get nothing.

The summary is ok once I select a specific job, but when all jobs are required to be shown, the figures are wrong.

Thanks.

9 Replies
swuehl
MVP
MVP

I assume that the Sum() function is working properly, but it's something in your data that gives different results when you are using for example dimensions.

What is the lower bar chart showing? I've noticed that it also shows differences compared to the table chart.

Example: M003056

stephenhasson
Creator
Creator
Author

Thanks for your reply Stefan. You are right, I'm even further away that I thought.. I've reattached a screen dump.

My bottom table was intended to show the breakdown of hours against the multiple jobs (and operation therein), whereas the top was to show the same figure but totalled by the the business area.

In both cases, using SUM, I though the figures just calculated based on the number of records that had been selected, i.e. dynamically calculated themselves. Have I missed a trick somewhere? Thanks again.

stephenhasson
Creator
Creator
Author

!

swuehl
MVP
MVP

What is the expression you are using in the table chart? I assume it's the same you are using in the bar chart, using the Sum(EstProdHours).

How does your data model look like? Can you post a screenshot (you can directly add images to the thread, no need to use Word documents)? Or even better, could you post your sample QVF?

swuehl
MVP
MVP

Could you also select the different operators and compare the values in the table and bar chart?

swuehl
MVP
MVP

And, could you create a KPI object using

=Count(JobNum)

It should return 4, right?

stephenhasson
Creator
Creator
Author

Thanks.

I'm trying to use Sum(EstProdHours). I thought it was working but it appears only to work on the grid, not on the bar graphs. I've attached the QVF file here.


thank you for your help

swuehl
MVP
MVP

The Sum(EstProdHours) is working for me in all graphs.

It seems to me that you are loading the fact table twice, hence doubling your numbers.

In your table chart, you were just using

EstProdHours

as expression, which will be interpreted as Only(EstProdHours). Hence you will see here not the sum, but the single unique value per your dimensions.

The double load takes place on your second script tab, an autogenerated script? Not sure what you are trying to achieve here.

edit:

Use Aggregation Functions!

The Only Function

stephenhasson
Creator
Creator
Author

Hi, thanks very much for your help. I wanted to created an additional selection criteria in the data editor, which works, but it did appear to keep creating another script. I think I’ve removed this now and the one I changed shows as locked, which I’ll leave.

I appreciate you support, this is very new to me, so appear to be a lot to learn when you start into it.

I wasn’t able to reply to the message (can you only do this for so many iterations?), hence the email. I hope it makes it to you.

Regards,

Stephen

Extension : 7060

Telephone : +44 1294 487060