Incorrect KPI calculation

[Amit_B]

Hi,

I have a problem in the data model and KPIs.
The Facts table consists of several tables (concatenate), and there is a dimension table that contains flags.
I want to count the amount of values in a packet according to a certain flag and I don't get data.

How can I solve this?

Simple example

Fact:

SourceTable	num(Date)	StoreID	OrderID	ItemID	Qty	Amount	GoalAmount	Key
Orders	44444	001	1001	a001	1	100	-	44444 | 001
Orders	44444	001	1001	b002	1	150	-	44444 | 001
Orders	44444	001	1002	a001	2	200	-	44444 | 001
Orders	44444	002	2001	c003	5	50	-	44444 | 002
Orders	44444	002	2002	a001	4	350	-	44444 | 002
Orders	44444	001	1003	b002	2	300	-	44444 | 001
Goals	44444	001	-	-	-	-	700	44444 | 001
Goals	44444	002	-	-	-	-	800	44444 | 002

Dim:

Key	flag1	flag2	flag3
44444 | 001	1	1	0
44444 | 002	0	1	1
44444 | 003	1	1	1

Sum({<flag1={1}>}Qty) = 6 --> I received 0.
Sum(Qty) when filtered on flag1=1 --> I received 0.

My final KPI should be like that:
Avg(Aggr(Sum({<flag1={1}>}Qty),Date))

Thanks!!

[Vegar · Accepted solution]

The data set provides 6 as expected for the 

Sum({<flag1={1}>}Qty) = 6 

I loaded your example into Qlik Sense using this script.

FACT:
LOAD 
  SourceTable,
  Date(Date) as Date,
  StoreID,
  OrderID,
  ItemID,
  Qty,
  Amount,
  GoalAmount,
  Key
inline [
  SourceTable, Date, StoreID, OrderID,ItemID, Qty,Amount, GoalAmount, Key
  Orders, 	44444, 001, 	1001,	a001, 	1, 	100, 	-, 			44444 | 001
  Orders, 	44444, 001, 	1001, 	b002, 	1, 	150, 	-, 			44444 | 001
  Orders, 	44444, 001, 	1002, 	a001, 	2, 	200, 	-, 			44444 | 001
  Orders, 	44444, 002, 	2001, 	c003, 	5, 	50, 	-, 			44444 | 002
  Orders, 	44444, 002, 	2002, 	a001, 	4, 	350, 	-, 			44444 | 002
  Orders, 	44444, 001, 	1003, 	b002, 	2, 	300, 	-, 			44444 | 001
  Goals, 		44444, 001, 	-, 		-, 		-, 	-, 		700, 		44444 | 001
  Goals, 		44444, 002, 	-, 		-, 		-, 	-, 		800, 		44444 | 002
](delimiter is ',');

DIM:
LOAD 
  Key,
  flag1,
  flag2,
  flag3
inline [
  Key, 		flag1, 	flag2, 	flag3
  44444 | 001,1, 		1, 		0
  44444 | 002,0, 		1, 		1
  44444 | 003,1, 		1, 		19
] (delimiter is ', ')
;

exit Script

[Amit_B]

I found my error.

The key was not connected because in one table I used Date instead of Num(Date).