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: 
Not applicable

Issue with Summing Values

Hello Everyone,

I have the following issue.

I have a data set that looks like this (I have blacked out some of it for reasons of privacy).

Screenshot (100).png

When loaded on Qlik, it looks like this:

Screenshot (101).png

The issue is that the Sum([Approved Budget]) should be giving a value of 216023. Instead, it gives a value of 1296138.

I think I know why this is the case. It is because I have had to unpivot the table as the excel data is in a crosstable format.

As such, there are more values in the approved budget column than there should be.

I tried to remedy this problem using the distinct function. This, however, doesn't solve the issue because some of the line items have the same approved budget.

Does anyone have an idea of how to solve this issue. I am a bit clueless as to what to do.

1 Solution

Accepted Solutions
Not applicable
Author

Hi Alison,

correct me if i'm wrong, but by the looks of it, the approved budget column is the total for the six months?

If this is the case, then I would split it up into two tables,

     table one:

          - budget item.(Key)

          - approved budget.

     table two(Crosstable):

          - budget item(Key)

          - project date

          - project amount


KG,


Nick

View solution in original post

5 Replies
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi Alison,

in order to understand what might cause such a problem, I'd like to see the data load script. Even better, if you could create a "scrambled" data example and post it along with your app, it would be even easier to troubleshoot.

You certainly don't want to use DISTINCT within a Sum() because some of the amounts are legitimately repeated, and you don't want to lose them.

Cheers,

Oleg Troyansky

Upgrade your Qlik skills with my book QlikView Your Business: An Expert Guide to Business Discovery with QlikView and Qlik Sense

Not applicable
Author

Hello Oleg,

Thank you for your response.

I can provide you with the data load script. Here it is:

Screenshot (102).png

Screenshot (103).png

Screenshot (104).png

Not applicable
Author

Hi Alison,

correct me if i'm wrong, but by the looks of it, the approved budget column is the total for the six months?

If this is the case, then I would split it up into two tables,

     table one:

          - budget item.(Key)

          - approved budget.

     table two(Crosstable):

          - budget item(Key)

          - project date

          - project amount


KG,


Nick

Not applicable
Author

Hello,

Here's the data and the app.

Hope this helps.

Not applicable
Author

Hi Nick,

Good thinking. I just gave it a go and it, of course, works.

Thanks a lot,

Alison