topic Re: Numbers doubling up in QlikView

Numbers doubling up

paul_ripley — Sat, 16 Nov 2024 02:27:08 GMT

Hello

I have a simple report that extract data from the database.

I filter on one period, asset and account code and thr amount 1698 is correct in the table box

When use the expression sum(amount) it doubles the value to 3397.

Do you have any idea why this may be?

I attache the qvw

Many thanks

Paul

Re: Numbers doubling up

lironbaram — Wed, 18 Sep 2019 10:20:41 GMT

usually it means you have duplicated records

Re: Numbers doubling up

tresB — Wed, 18 Sep 2019 10:32:39 GMT

Qlikview table box shows only unique combination of records. So that might not help you get the right picture here. Try 'show frequency' in amount listbox; you would see 2 as frequency for your selected record.

Re: Numbers doubling up

Victor_Alumanah — Wed, 18 Sep 2019 11:58:06 GMT

Hi Paul,

The reason why your figures are doubling up is because you have loaded the data in twice, I would suggest reviewing your script to find out what has caused it, or even better clear the report of any data and reload to see how many rows are loaded.

At the moment you can see 148,437 rows, but there are actually 296,872 rows(double). Go to table viewer and hover over the FixedAssets table and you will see the 296,872 number of rows displayed.

I hope this is helpful.

Victor

Re: Numbers doubling up

sunny_talwar — Wed, 18 Sep 2019 11:58:11 GMT

Did you forget to add the condition on Year may be? because without a condition on Year, you might be looping over 2 years pulling the same data twice....

SELECT k.client, k.asset_id, a.description AS asset_descr, a.date_from, k.asset_group, k.dim_1, c.description AS asset_group_desc, k.trans_type, k.voucher_type, k.account_type, k.period AS gl_period, k.trans_date, SUM (k.amount) AS amount, SUM (k.value_3) AS value_3, k.account FROM aatasset a, agldescription c, aattrans k WHERE k.client = a.client AND k.asset_id = a.asset_id AND a.status = 'N' AND k.client = c.client AND a.asset_group = c.dim_value AND c.attribute_id = 'G10' AND year = '$(vYear)' GROUP BY k.client, k.asset_id, a.description, a.date_from, k.asset_group, k.dim_1, c.description, k.trans_type, k.voucher_type, k.account_type, k.period, k.trans_date, k.account ORDER BY asset_group_desc, k.asset_id;

Re: Numbers doubling up

paul_ripley — Wed, 18 Sep 2019 12:39:25 GMT

Thanks , i think it is something to do with that

Re: Numbers doubling up

paul_ripley — Wed, 18 Sep 2019 12:40:12 GMT

Thanks, that is the reason ie that i am picking up the year twice. I will try to work out how to fix it