Sum value differs when we use the dimension as KPI in Qliksense

[dharis]

Hello, 

When I use column Item Value_EGNetUSD  with SUM function ( sum(distinct[Item Value_EGNetUSD]) I do get a sum total value = 9.6 K which differs when I use the same column as KPI (Key performance indicator)  = 7.26k. Please can anyone let me know why is this difference. Attached example with 10 lines of data for reference. Your assist is highly appreciated 

KPI value - 7.26K

Manually downloaded and check - 9.5K

 KPI as 

[GaryGiles]

The difference between the sum in the KPI vs the sum in the table is due to your use of "distinct" in your sum.  In the KPI, the distinct is applied to [Item Value_EGNetUSD] for the entire set.  In the table, it is able to each combination of [SAP ON], [SourceRegion] and [Item Value_EGNetUSD], which means all values will be included.  If a value occurs more than once in the data set, it will only be included once in the KPI with "distinct".

If you remove the word "distinct" from the KPI you should get a value that matches the table.

Is there a reason that you used "distinct"?

[dharis]

Hi GaryGiles, 

Thanks for your reply. The issue is when I use the SUM Function the revenue numers with raw data and KPI does not matches again. Please check below screen. 

Value in KPI with SUM function. Is there a way to get the revenue number that has a value reflecting in coulumn C = 9.3K in KPI 

Value when I use KPI with SUM() = 14.3k 

Value in Raw data extraction without SUM function = 9.3 K 

[GaryGiles]

What is the level of aggregation in the table for the value [Item Value_EGNetUSD]?  It seems like you have multiple rows with the same value, thus the use of Distinct.  Is the data summarized to the [SAP ON] field?

[dharis]

The thing surprises is we could not track any duplicates in Raw data extracts.  We do use SAP_SOLI as primary key - Below is the script I use to load the data from QVD from various tables this may give you an Idea

Main:
LOAD
    SAP_SOLI,
    "Item status",
    "Order type",
    "SAP ON",
    "SAP item #",
    "Legacy ON",
    Product,
    "Product description",
    PL,
    BU,
    "Ship to Party",
    "Order create dt",
    CRD,
    "GI dt",
    Plant,
    "SHP point",
    "Build trigger dt",
    Complexity,
    "Compl. Del.",
    "Hold category",
    "Hold mapping",
    "Hold desc.",
    "Future CRD y/n",
    "HLI y/n",
    "Unit type",
    "SOC vs Item Create",
    "Ship-to country",
    ESD,
    FactoryShipDt,
    ScBlCat,
    ObProcessDate,
    "Order Qty",
    "SAP HLI",
    "Revenue Orders",
    "Item clean dt",
    SourceRegion
FROM [lib://HPE EG QVD Location/NPI/Orderbook/OB_COM_MAIN.qvd]
(qvd);

Left Join(Main)

LOAD
    SAP_SOLI,
   "SNP Process Calendar Date DD MM YYYY Code",
    PROFIT_CENTER_L1_NAME,
    Sum(EGNetUSDAmount) as "(Item Value_EGNetUSD)"
 FROM [lib://HPE EG QVD Location/NPI/Orderbook/OB_COM_FINANCE.qvd]
(qvd);

Left Join(Main)

LOAD
SAP_SOLI,
TotalRecommit
FROM [lib://HPE EG QVD Location/NPI/Orderbook/OB_COM_RECOMIT.qvd]
(qvd);

Left Join(Main)

LOAD
SAP_SOLI,
    "Miss Avail Dt",
    "Miss Part Desc."
FROM [lib://HPE EG QVD Location/NPI/Orderbook/OB_COM_SHORTAGES.qvd]
(qvd);

Store * from [Main] into [lib://HPE EG QVD Location/NPI/Orderbook/OB_COM_FINAL.qvd]
(qvd);

[GaryGiles]

So, there is a field named Sum(EGNetUSDAmount) in OB_COM_FINANCE.qvd??

How is that qvd built?  Does that data in that qvd match your raw data?

My guess is that the granularity of that qvd is different than you are expecting.  Is it aggregate to only SAP_SOLI,
   "SNP Process Calendar Date DD MM YYYY Code",    PROFIT_CENTER_L1_NAME?

[dharis]

finance QVD is extracted from Cube from analytical team and share  feed everyday, here SAPSOLI (SAPorder + SAP item) are primary key / unique key where revenue numbers are associated /linked. that's the reason we join tables using SAPSOLI as primary key. Hope this answers your question. Let me know if you need more insight on this? again Thanks for your time and help. 

[dharis · Accepted solution]

there is not field SUM(Item_EGNetUSD) we create that for our KPI 

QVD is automated Feed, from Cube. 

revenue numbers are associated to SAPSOLI each line item for example SAP order = 32109010 and Item number 0010, 0020,0030 so revenue number for each line item will be assigned as 321090100010, 32090100020,321090100030....... when I check for duplicates in SAPSOLI I could not trace one said so, not sure how is the numbers are getting multiplied for only few line items and not for all when I use SUM(). Looks there are duplicate in RAW. Is there a way to match the numbers from colum C (Item_Value EGNetUSD and KPI . Please let me know