topic Re: Sum value differs when we use the dimension as KPI in Qliksense in App Development

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

dharis — Tue, 21 Dec 2021 21:56:36 GMT

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

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

GaryGiles — Fri, 16 Apr 2021 11:28:35 GMT

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"?

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

dharis — Fri, 16 Apr 2021 13:59:55 GMT

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

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

GaryGiles — Fri, 16 Apr 2021 14:09:21 GMT

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?

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

dharis — Fri, 16 Apr 2021 14:44:39 GMT

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

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

GaryGiles — Fri, 16 Apr 2021 15:39:18 GMT

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?

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

dharis — Sat, 17 Apr 2021 04:15:14 GMT

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.

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

dharis — Sat, 17 Apr 2021 04:22:13 GMT

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