topic Re: problems with sum in App Development

problems with sum

Wed, 09 Dec 2015 10:12:44 GMT

Hey everyone i have a problem , i am buliding a sales report,

once i am trying to sum the incomes coulmn for some reason its duplicates my incomes ?

anyone have suggetions what should i check?

Re: problems with sum

sunny_talwar — Wed, 09 Dec 2015 10:16:10 GMT

Pure speculation -> It could be because you might be joining another table in the script which is leading to duplicate incomes. You can either leave your table unjoined and let QlikView handle the join or you can use an aggregate function.

Re: problems with sum

Thu, 10 Dec 2015 10:14:31 GMT

Re: problems with sum

Thu, 10 Dec 2015 10:16:16 GMT

unfortunately it seems that the problem is still there ....

Re: problems with sum

sunny_talwar — Thu, 10 Dec 2015 10:36:56 GMT

Have you already ensured that there are no duplicates in your data source?

Re: problems with sum

Thu, 10 Dec 2015 11:20:31 GMT

trying to show my script:

ACCDATES:

LOAD

CURDATE AS ACC_CURDATE,

COSTFLAG AS ACC_COSTFLAG,

COSTDATE AS ACC_COSTDATE

FROM [lib://Clinton key/Accdates.qvd]

(qvd);

DOCS:

LOAD

DOC AS DOC_DOC,

TYPE,

T$PERCENT,

DOCNO,

CUST,

AGENT

FROM [lib://Clinton key/Docs.qvd]

(qvd);

AGENTS:

LOAD

AGENT AS AGENT_AGENT,

AGENTCODE ,

AGENTNAME AS AGENT_AGENTNAME

FROM [lib://Clinton key/Agents.qvd]

(qvd);

BRANCHES:

LOAD

COMPANY&'_'&BRANCH AS BRANCH_BRANCH,

BRANCHNAME,

BRANCHDES,

COMPANY AS BRANCH_COMPANY

FROM [lib://Clinton key/Branches.qvd]

(qvd);

CUSTOMERS:

LOAD

CUSTNAME AS CUST_CUSTNAME,

CUST AS CUST_CUST,

CUSTDES AS CUST_CUSTDES,

CTYPE AS CUST_CTYPE,

TERRITORY AS CUST_TERRITORY,

CUSTSTAT AS CUST_CUSTAT

// AGENT AS CUST_AGENTNAME

// במקרה שלנו לא אפקטיבי רוצים את הסוכן מהחשבונית עצמה

FROM [lib://Clinton key/customers.qvd]

(qvd)

WHERE CUSTSTAT <> -5;

Parts_TMP: //טבלת פריטים

LOAD

PART AS PART_PART,

PARTNAME,

TYPE AS PART_TYPE ,

PARTDES,

UNIT AS PART_UNIT,

PARTSTAT AS PART_STATUS,

PRICE AS PART_PRICE ,

LASTPRICE AS PART_LASTPRICE ,

COST AS PART_COST,

FAMILY AS PART_FAMILY,

SECONDCOST AS PART_SECONDCOST,

COSTQUANT AS PART_COSTQUANT

FROM [lib://Clinton key/parts.qvd]

(qvd);

Left Join(Parts_TMP)

LOAD

FAMILY AS PART_FAMILY,

FAMILYNAME as PART_FAMILYNAME,

SELLFLAG as PART_FAMSELLFLAG,

FAMILYTYPE AS FAMILY_TYPE

FROM [lib://Clinton key/Family.qvd]

(qvd);

PARTS: // טבלת פריטים קבוע

LOAD

PART_PART,

PARTNAME as PART_PARTNAME,

PART_TYPE,

PARTDES,

PART_COST,

PART_FAMSELLFLAG,

PART_FAMILY,

PART_FAMILYNAME,

// PART_FAMILYDES,

FAMILY_TYPE,

PART_SECONDCOST,

PART_COSTQUANT,

PART_STATUS

RESIDENT Parts_TMP

Where PART_STATUS <> -2;

;

DROP Table Parts_TMP;

CURRENCIES:

//מטבעות ושערי חליפין

LOAD

NAME,

CODE AS CUR_CODE,

CURRENCY AS CUR_CURRENCY ,

EXCHANGE AS CUR_EXCHANGE ,

EXCHDATE AS CUR_EXCHDATE

FROM [lib://Clinton key/Currency.qvd]

(qvd);

CURREG:

//שערי חליפין

LOAD

CURRENCY AS CURREG_CURRENCY,

EXCHANGE AS CURREG_EXCHANGE ,

CURDATE AS CURREG_CURDATE

FROM [lib://Clinton key/CurReg.qvd]

(qvd);

Re: problems with sum

sunny_talwar — Thu, 10 Dec 2015 11:24:12 GMT

and what is your expression?

Re: problems with sum

Thu, 10 Dec 2015 11:24:48 GMT

INV_SALES_TEMP:

LOAD

Date(IVDATE/60/24+32143,'DD/MM/YYYY') AS INV_IVDATE,

IVNUM AS INV_INVNUM,

DOC AS INV_DOC,

IV,

QPRICE AS INV_QPRICE,

TOTPRICE AS INV_TOTPRICE ,

CURRENCY AS INV_CURRENCY,

CUST AS CUST_CUST,

DEBIT,

FINAL AS INV_FINAL,

ORD AS INV_ORD,

DISCOUNT AS INV_DISCOUNT,

PAYDATE AS INV_PAYDATE,

FNCTRANS AS INV_FNCTRANS,

BOOKNUM AS INV_BOOKNUM,

AGENT AS INV_AGENT,

"TYPE" AS INV_TYPE,

CHECKING AS INV_CHECKING,

IMPFILE AS INV_IMPFILE,

STORNOFLAG AS INV_STORNOFLAG ,

BRANCH AS INV_BRANCH

FROM [lib://Clinton key/Invoices.qvd]

(qvd)

where FINAL = 'Y'

and STORNOFLAG <> ('Y')

and Date (IVDATE/60/24+32143,'DD/MM/YYYY') >= '01/01/2010'

and

(TYPE='A' //חשבונית מס

OR TYPE='C' //חשבונית מרכזת

OR TYPE='F'

OR TYPE='E'); //חשבונית מס קבלה; //חשבונית חול;

//פרטיי תנועת יומן

LEFT JOIN (INV_SALES_TEMP)

LOAD

FNCTRANS AS INV_FNCTRANS,

FNCNUM,

FINAL AS FNC_FINAL,

CURDATE AS FNC_CURDATE,

FNCDATE,

EXCHANGE3 AS FNC_EXCHANGE3,

CURRENCY3 AS FNC_CURRENCY3,

IVNUM AS FNC_IVNUM,

EXCHANGE2 AS FNC_EXCHANGE2

FROM [lib://Clinton key/Fnctrans.qvd]

(qvd) Where FINAL ='Y';

// אני יכול להוריד את זה במידה ואני רוצה חשבוניות זמניות ולא סופיות

//שורות החשבונית

LEFT JOIN (INV_SALES_TEMP)

LOAD

IV,

PART AS IV_PART,

PRICE AS IV_PRICE,

QUANT/1000 AS IV_QUANT,

QPRICE AS IV_QPRICE,

"CURRENCY" AS IV_CURRENCY,

"LINE" AS IV_LINE,

ORDI AS IV_ORDI,

"T$PERCENT" AS IV_PERCENT,

UDATE AS IV_UDATE,

KLINE AS IV_KLINE,

TQUANT/1000 AS IV_TQUANT,

TUNIT,

TRANS AS IV_TRANS,

TOTPERCENT AS IV_TOTPERCENT ,

DUTYCOST AS IV_DUTYCOST,

COSTC AS IV_COSTC,

// IVCOST AS IV_IVCOST,

(IF(ISNULL(IVCOST)=-1,0,IVCOST)) AS IV_IVCOST,

TYPE AS IV_TYPE,

CREDITCOST AS IV_CREDITCOST ,

BRANCH AS IV_BRANCH,

IVDATE AS IV_IVDATE,

ICURRENCY AS IV_ICURRENCY ,

IEXCHANGE AS IV_IEXCHANGE ,

CREDITFLAG AS IV_CREDITFLAG ,

AGENT AS IV_AGENT,

COMMISSION AS IV_COMMISSION

FROM [lib://Clinton key/InvoiceItems.qvd]

(qvd)

WHERE

(TYPE='A' //חשבונית מס

OR TYPE='C' //חשבונית מרכזת

OR TYPE='F' //חשבונית חול

OR TYPE='E' )//חשבונית מס קבלה)

;

// נתונים נוספים לשורת חשבונית

LEFT JOIN (INV_SALES_TEMP)

LOAD

IV,

KLINE AS IV_KLINE,

COSTC2 AS IVA_COSTC2 ,

COSTC3 AS IVA_COSTC3,

COSTC4 AS IVA_COSTC4,

COSTC5 AS IVA_COSTC5,

TOTPRICE AS IVA_TOTPRICE ,

IVTAX AS IVA_IVTAX,

EXTCOST AS IVA_EXTCOST,

EXTCOST2 AS IVA_EXTCOST2,

BONUSFLAG AS IVA_BONUSFLAG

FROM [lib://Clinton key/InvoiceItemsa.qvd]

(qvd);

// נתונים מכרטיס לקוח

LEFT JOIN (INV_SALES_TEMP)

LOAD

CUST_CUST,

CUST_CUSTNAME AS INV_CUSTNAME,

CUST_CUSTDES AS INV_CUSTDES

// במקרה שלנו לא אפקטיבי רוצים את הסוכן מהחשבונית עצמה

// CUST_AGENTNAME

RESIDENT

CUSTOMERS;

//נתונים מכרטיס פריט משורשרים לטבלת המכירות

LEFT JOIN (INV_SALES_TEMP)

LOAD

PART_PART AS IV_PART,

PART_COSTQUANT,

PART_COST as IV_PART_COST,

PART_FAMILYNAME as IV_FNAME ,

PART_SECONDCOST

RESIDENT PARTS;

// נתונים משורות התנועה המקשורת לחשבונית - עלות וכדומה

LEFT JOIN (INV_SALES_TEMP)

LOAD

(IF(ISNULL(TRANS)=-1,0, TRANS)) AS IV_TRANS,

DOC AS TRANS_DOC,

PART AS TRANS_PART,

// QUANT/1000 AS TRANS_QUANT,

(QUANT/1000 *(IF (TYPE='N',(-1),1))) AS TRANS_QUANT,

ORDI AS TRANS_ORDI,

CUST AS TRANS_CUST,

PRICE AS TRANS_PRICE,

IV,

COST AS TRANS_COST,

// מייצג עלות TRANS_COST

SECONDCOST AS TRANS_SECONDCOST,

BRANCH AS TRANS_BRANCH,

CURRENCY AS TRANS_CURRENCY,

COGFLAG AS TRANS_COSTFLAG,

CURDATE AS TRANS_CURDATE

FROM [lib://Clinton key/Trans.qvd]

(qvd);

//שער מטבע לתאריך החשבונית ולמטבע השורה

Left JOIN (INV_SALES_TEMP)

LOAD

CUR_CURRENCY AS INV_CURRENCY,

CUR_EXCHANGE

Resident CURRENCIES;

LEFT JOIN (INV_SALES_TEMP)

LOAD

Date (CURREG_CURDATE/60/24+32143,'DD/MM/YYYY') AS INV_IVDATE,

CURREG_CURRENCY AS INV_CURRENCY,

CURREG_EXCHANGE

Resident CURREG;

left JOIN (INV_SALES_TEMP)

LOAD

CUR_EXCHANGE AS CUR_EXCHANGE2

Resident CURRENCIES Where CUR_CURRENCY = -2;

LEFT JOIN (INV_SALES_TEMP)

LOAD

Date ( CURREG_CURDATE/60/24+32143,'DD/MM/YYYY') AS INV_IVDATE,

CURREG_EXCHANGE AS CURREG_EXCHANGE2

Resident CURREG Where CURREG_CURRENCY = -2;

// POSSIBLE EURO COIN

Left JOIN (INV_SALES_TEMP)

LOAD

CUR_EXCHANGE AS CUR_EXCHANGE3

Resident CURRENCIES

Where CUR_CURRENCY =$(vForeignCurrency);

// שיניתי כי לא עבד בפעם הראשונה לא זהה את השדה

// POSSIBLE EURO COIN

left JOIN (INV_SALES_TEMP)

LOAD

Date ( CURREG_CURDATE/60/24+32143,'DD/MM/YYYY')AS INV_IVDATE ,

CURREG_EXCHANGE AS CURREG_EXCHANGE3

Resident CURREG

Where CURREG_CURRENCY =$(vForeignCurrency);

// LINE 183 IN THE SALES CHART//

LEFT JOIN(INV_SALES_TEMP)

LOAD

ACC_COSTFLAG,

DATE((NUM(MAX(ACC_COSTDATE)))/60/24+32143,'DD/MM/YYYY') AS MAXACC_COSTDATE

RESIDENT ACCDATES

WHERE ACC_COSTDATE<>0

GROUP BY ACC_COSTFLAG;

DROP TABLE ACCDATES;

INV_SALES:

LOAD

CUST_CUST AS INV_CUST,

INV_IVDATE AS IVDATE,

INV_TOTPRICE,

IF (INV_TOTPRICE > 0 AND DEBIT ='D','pos','neg') AS INV_TOTSIGN,

INV_INVNUM,

// CUST_AGENTNAME AS INV_AGENTNAME,

// במקרה שלנו לא אפקטיבי רוצים את הסוכן מהחשבונית עצמה

//שיניתי בכדי למנוע לופ//

IV_AGENT,

IV_BRANCH,

INV_TYPE,

IV_PART,

IV_PRICE,

TRANS_COST,

//הוספתי בכדי לקבל את העלות//

IV_QUANT,

IV_QUANT*IF(DEBIT ='C',-1,1) AS RIV_QUANT,

IV_PERCENT,

// IV_FAMILYFTCODE,

IV_COMMISSION,

IV_IVCOST,

(IF(ISNULL(IV_TRANS) =-1,0,IV_TRANS)) AS IV_TRANS,

IV_CREDITFLAG,

// מכפיל לזיכויים//

// ככה מוודאים שחשבוניות זיכוי בר

(IF(DEBIT='C',(-1),1)) AS IVCAL_CREDIT,

// מחיר בשקלים לשורה

(IV_IVCOST * (IF (DEBIT ='C',(-1),1)) * (IF(INV_CURRENCY = (-1) ,1,IF(FNC_CURRENCY3 = INV_CURRENCY,FNC_EXCHANGE3,

if(CURREG_EXCHANGE > 0 , CURREG_EXCHANGE, CUR_EXCHANGE )))))

AS NIS_TOTPRICE,

// מחיר בדולרים לשורה

(IV_IVCOST * (IF (DEBIT='C',(-1),1)) * (IF(INV_CURRENCY = (-1) ,1,IF(FNC_CURRENCY3=INV_CURRENCY,FNC_EXCHANGE3,

IF(CURREG_EXCHANGE > 0 , CURREG_EXCHANGE, CUR_EXCHANGE ))))) / IF(FNC_EXCHANGE2 > 0 , FNC_EXCHANGE2, IF(CURREG_EXCHANGE2 > 0 ,

CURREG_EXCHANGE2 , CUR_EXCHANGE2))

AS DOLLAR_TOTPRICE,

// // מחיר במטבע שלישי

// POSSIBLE EURO COIN

(IV_IVCOST * (IF (DEBIT='C',(-1),1)) * (IF(INV_CURRENCY = (-1) ,1,IF(FNC_CURRENCY3 = INV_CURRENCY,FNC_EXCHANGE3,

IF(CURREG_EXCHANGE > 0 , CURREG_EXCHANGE, CUR_EXCHANGE ))))) / IF(CURREG_EXCHANGE3 > 0 ,

CURREG_EXCHANGE3 , CUR_EXCHANGE3)

AS CURRENCY_TOTPRICE,

//חישוב רווח ועלות לשורה

// במרכאות

// TRANS_COST,TRANS_COSTFLAG,DATE(TRANS_CURDATE/60/24 +32143,'DD/MM/YYYY') AS TRANS_CURDATE ,MAXACC_COSTDATE,IV_PART_COST,

//עלות בשקל לתנועה

// ILS FOR A TRANSCATION

IF ((IsNull(TRANS_COST)=0 OR TRANS_COSTFLAG = 'M') OR (IV_TRANS <> 0 AND TRANS_CURDATE <=MAXACC_COSTDATE),

TRANS_COST * (IF (TRANS_QUANT * IV_TQUANT < 0.0,(-1),1)),

(IV_PART_COST * IV_QUANT / PART_COSTQUANT * (IF (DEBIT='C',(-1),1)) * (IF (IV_CREDITFLAG='Y',0,1)))) AS NIS_COST,

// //עלות בדולר לשורת תנועה

IF ((IsNull(TRANS_SECONDCOST)= 0 OR TRANS_COSTFLAG = 'M') OR (IV_TRANS <> 0 AND TRANS_CURDATE <= MAXACC_COSTDATE),

TRANS_SECONDCOST * (IF (TRANS_QUANT * IV_TQUANT < 0.0,(-1),1)),

PART_SECONDCOST * IV_QUANT / PART_COSTQUANT * (IF (DEBIT ='C',(-1),1)) * (IF (IV_CREDITFLAG = 'Y',0,1))) AS DOLLAR_COST,

// // // עלות במטבע שלישי לשורת תנועה

// POSSIBLE EURO COIN

(IF ((IsNull(TRANS_SECONDCOST)=0 OR TRANS_COSTFLAG = 'M') OR (IV_TRANS <> 0 AND TRANS_CURDATE <=MAXACC_COSTDATE),

TRANS_COST * (IF (TRANS_QUANT * IV_TQUANT < 0.0,(-1),1)),

IV_PART_COST * IV_QUANT / PART_COSTQUANT * (IF (DEBIT='C',(-1),1)) * (IF (IV_CREDITFLAG='Y',0,1))) ) / IF(CURREG_EXCHANGE3 > 0 ,CURREG_EXCHANGE3 , CUR_EXCHANGE3) AS CURRENCY_COST,

// // עלויות ייצוא

// // המשתנה מייצג את השער שברצוננו לקחת האם מתוך תיק הייצוא (שורת החשבונית) או לפי שער המרה לאותו היום

// ILS

(IV_DUTYCOST * (IF (DEBIT='C',(-1),1)) * (IF(($(vEXPORTEXCHANGE)=1) AND IV_DUTYCOST <> 0,IV_IEXCHANGE,(IF(INV_CURRENCY = (-1) ,1,IF(FNC_CURRENCY3=INV_CURRENCY,FNC_EXCHANGE3,

if(CURREG_EXCHANGE > 0 , CURREG_EXCHANGE, CUR_EXCHANGE ))))))) AS NIS_DUTYCOST,

// DOLLAR

(IV_DUTYCOST * (IF (DEBIT='C',(-1),1)) * (IF(($(vEXPORTEXCHANGE)=1) AND IV_DUTYCOST <> 0,IV_IEXCHANGE,(IF(INV_CURRENCY = (-1) ,1,IF(FNC_CURRENCY3=INV_CURRENCY,FNC_EXCHANGE3,

if(CURREG_EXCHANGE > 0 , CURREG_EXCHANGE, CUR_EXCHANGE )))))))/ IF(FNC_EXCHANGE2 > 0 , FNC_EXCHANGE2, IF(CURREG_EXCHANGE2 > 0 ,

CURREG_EXCHANGE2 , CUR_EXCHANGE2)) As DOLLAR_DUTYCOST,

// POSSIBLE EURO COIN

(IV_DUTYCOST * (IF (DEBIT='C',(-1),1)) * (IF(($(vEXPORTEXCHANGE)=1) AND IV_DUTYCOST <> 0,IV_IEXCHANGE,(IF(INV_CURRENCY = (-1) ,1,IF(FNC_CURRENCY3=INV_CURRENCY,FNC_EXCHANGE3,

if(CURREG_EXCHANGE > 0 , CURREG_EXCHANGE, CUR_EXCHANGE )))))))/ IF(CURREG_EXCHANGE3 > 0 ,CURREG_EXCHANGE3 , CUR_EXCHANGE3) As CURRENCY_DUTYCOST,

//עלויותת נוספות

//ILS

(IVA_EXTCOST * (IF (DEBIT='C',(-1),1)) * (IF(IV_CREDITFLAG ='Y',0,1))) AS NIS_EXTCOST,

// DOLLAR

(IVA_EXTCOST * (IF (DEBIT='C',(-1),1)) * (IF(IV_CREDITFLAG ='Y',0,1))) / IF(FNC_EXCHANGE2 > 0 , FNC_EXCHANGE2, IF(CURREG_EXCHANGE2 > 0 ,

CURREG_EXCHANGE2 , CUR_EXCHANGE2)) As DOLLAR_EXTCOST,

// POSSIBLE EURO COIN

(IVA_EXTCOST * (IF (DEBIT='C',(-1),1)) * (IF(IV_CREDITFLAG ='Y',0,1))) / IF(CURREG_EXCHANGE3 > 0 ,CURREG_EXCHANGE3 , CUR_EXCHANGE3) As CURRENCY_EXTCOST,

//מפתח בטבלת מכירות

CUST_CUST&'_'&IV_AGENT&'_'&IV_BRANCH&'_'&IV_PART&'_'&INV_IVDATE AS SALES_KEY

RESIDENT INV_SALES_TEMP

WHERE

(INV_TYPE='A' //חשבונית מס

OR INV_TYPE='C' //חשבונית מרכזת

OR INV_TYPE='F' //חשבונית חול

OR INV_TYPE='E' )

// and IV_FNAME <> '900'

// 900 דגים וציוד

// חשבונית מס קבלה

// and Date (INV_IVDATE/60/24+32143,'DD/MM/YYYY') >= '01/01/2010'

ORDER BY

IV_AGENT,

IV_PART,

INV_IVDATE;

DROP TABLE INV_SALES_TEMP;

KEY_TABLE_TEMP: // טבלת מפתחות זמנית

LOAD AGENT_AGENT

Resident AGENTS;

CONCATENATE

LOAD

BRANCH_COMPANY AS COMP,

BRANCH_BRANCH

RESIDENT BRANCHES;

CONCATENATE

load

CUST_CUST ,

CUST_CTYPE AS CTYPE

RESIDENT CUSTOMERS;

CONCATENATE

Load

PART_PART

RESIDENT PARTS;

CONCATENATE

LOAD

INV_CUST AS CUST_CUST,

IV_PART AS PART_PART,

IV_AGENT AS AGENT_AGENT,

IV_BRANCH AS BRANCH_BRANCH,

IVDATE AS DATE

RESIDENT INV_SALES;

KEY_TABLE:

//טבלת מפתחות

LOAD

CUST_CUST,

PART_PART,

AGENT_AGENT,

BRANCH_BRANCH,

DATE,

CUST_CUST&'_'&AGENT_AGENT&'_'&BRANCH_BRANCH&'_'&PART_PART&'_'&DATE AS SALES_KEY

RESIDENT KEY_TABLE_TEMP;

DROP TABLE KEY_TABLE_TEMP;

LOAD distinct IVDATE AS DATE

Resident INV_SALES

where IVDATE >= $(vStart_Date);

Concatenate

load Distinct TRANS_CURDATE Resident Returns ;

CALENDAR: //תאריכון כולל הזמנות וחשבוניות

LOAD

distinct DATE,

YEAR (DATE) as YEAR,

MONTH (DATE) as MONTH,

WeekDay ( DATE)as WEEKDAY,

Day (DATE)as DAY,

week(DATE) as WeekNum,

'Q' &'-'& ceil(month(DATE)/3) as Quarter,

'H' &'/'& ceil(month(DATE)/6) as Half_Year,

'W' &'/'& ceil(DAY(DATE)/4) as Week_Month,

Date( MonthStart(DATE),'MMM-YYYY') as YearMonth,

if(DayNumberOfYear(today())>=DayNumberOfYear(DATE),'YTD') as YTD

Resident Calendar_build

where IsNull(DATE)=0;

;

DROP Table Calendar_build;

DROP Table DOCS;

DROP Table CURRENCIES;

DROP Table CURREG;

Re: problems with sum

Thu, 10 Dec 2015 11:27:00 GMT

when i am trying to get the sum of the income field IV_IVCOST this field duplicate itself:

meaning iv_ivcost= 100

sum( IV_IVCOST)=200

Re: problems with sum

reddy-s — Thu, 10 Dec 2015 11:40:55 GMT

Hi Jhonatan,

Can you check if the records are duplicated in the parts.qvd itself? I mean in the previous layer of QVD generation. I don't see any issue with the script.

Re: problems with sum

Thu, 10 Dec 2015 11:44:37 GMT

chechking

Re: problems with sum

reddy-s — Thu, 10 Dec 2015 11:49:47 GMT

Hi Jhonatan,

The reply which i gave was for the first code snippet you posted.

The Calculation engine does the joins for you and you do not have to explicitly do them. Is there a specific reason for the Left joins?

An having a large Fact table will impact the performance of the report as well.

Looking at the code I believe that one of the joins is duplicating the entries and hence the double in the figures.

Re: problems with sum

Thu, 10 Dec 2015 11:51:00 GMT

maybe the key table?

Re: problems with sum

reddy-s — Thu, 10 Dec 2015 11:56:22 GMT

Possibly yes!

Re: problems with sum

Sun, 13 Dec 2015 10:19:58 GMT

Hey Sunny ,

i haven't got any syn keys ,

so i am guessing there is no duplications....

you have any other ideas?

Re: problems with sum

reddy-s — Sun, 13 Dec 2015 10:29:00 GMT

Hi Jhonatan,

The Duplicates which we are referring here is not duplication of fields, instead duplication of the same record in a table:

ex:

Customer Product Price

X A 100

When you sum this up using a sum(Price): it would result in 200 because the record was duplicated .

Is this happening with your Fact table?

Re: problems with sum

Sun, 13 Dec 2015 10:33:35 GMT

Fact Table???

Re: problems with sum

reddy-s — Sun, 13 Dec 2015 10:36:37 GMT

Hi Jhonatan,

Yeah, I mean the table in which the measure is existing.

Just check if any of the records are being duplicated in the tables.

It would be easy to check from the Data Model Viewer .

Re: problems with sum

Sun, 13 Dec 2015 10:51:13 GMT

o.k , thank you , this my module viewer and i will check my fact table with , cust, sale key ,ivcost,totprice

Re: problems with sum

reddy-s — Sun, 13 Dec 2015 10:59:04 GMT

Yeah, Check the number of rows in the tables by selecting the table header and check the data view in the preview provided. You might find a table with more number of rows than you expected.

If you are not able to figure it out this way, simple create a straight table with the dimensions in the Fact table and you can figure it out.