Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
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.

Tags (3)
1 Solution

Accepted Solutions
Not applicable

Re: Issue with Summing Values

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
MVP & Luminary
MVP & Luminary

Re: Issue with Summing Values

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

Re: Issue with Summing Values

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

Re: Issue with Summing Values

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

Not applicable

Re: Issue with Summing Values

Hello,

Here's the data and the app.

Hope this helps.

Not applicable

Re: Issue with Summing Values

Hi Nick,

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

Thanks a lot,

Alison