topic Re: Issue with Summing Values in App Development

Issue with Summing Values

Fri, 25 Aug 2017 15:50:47 GMT

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).

When loaded on Qlik, it looks like this:

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.

Re: Issue with Summing Values

Oleg_Troyansky — Fri, 25 Aug 2017 17:19:23 GMT

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

Re: Issue with Summing Values

Tue, 29 Aug 2017 08:28:30 GMT

Hello Oleg,

Thank you for your response.

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

Re: Issue with Summing Values

Tue, 29 Aug 2017 11:43:26 GMT

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

Re: Issue with Summing Values

Tue, 29 Aug 2017 14:31:23 GMT

Hello,

Here's the data and the app.

Hope this helps.

Re: Issue with Summing Values

Tue, 29 Aug 2017 15:19:59 GMT

Hi Nick,

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

Thanks a lot,

Alison