Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
Hello Oleg,
Thank you for your response.
I can provide you with the data load script. Here it is:
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
Hello,
Here's the data and the app.
Hope this helps.
Hi Nick,
Good thinking. I just gave it a go and it, of course, works.
Thanks a lot,
Alison