topic Re: data model or calculation problem? in QlikView

data model or calculation problem?

Anonymous — Tue, 27 Mar 2018 14:09:23 GMT

Hello,

I try to create a straight table object with data from different fact types.

The dimensions are a mixture of data out of the user and activities table (two different fact types).

The values are calculated visits from a third table (fact type visits).

In my data model activities and visits are stored in the fact table (concatenated), the users are hanging outside linked by the user ID to the fact table.

The result should be a unique line per day with the user information, activity and the calculated visits for that day.

I tried to sum the visits in different ways. I displayed both formulas side by side but in general I need only one calculation per user.

When I use "sum (visits)" I get the visits only calculated on the second line per day for the fact type visits.

When I use "Sum (aggr( NODISTINCT sum( visits ), name, date)) " I get the correct number of visits calculated per day in the above line and a 2nd line for the fact type visits with a wrong sum like in my example.

Is there a way to hide the second line in combination with the aggr function or is this maybe a problem of my data model?

name	date	weekday	actvitiy	sum visits	sum aggr visitis
user1	01.12.2017	Fr	visit	0	8
user1	01.12.2017	Fr		8	104
user1	04.12.2017	Mo	visit	0	9
user1	04.12.2017	Mo		9	108
user1	05.12.2017	Tu	double visit	0	3
user1	05.12.2017	Tu		3	30
user1	06.12.2017	We	visit	0	9
user1	06.12.2017	We		9	108
user1	07.12.2017	Thr	visit	0	10
user1	07.12.2017	Thr		10	130
user1	08.12.2017	Fr	visit	0	9
user1	08.12.2017	Fr		9	108
user1	11.12.2017	Mo	visit	0	11
user1	11.12.2017	Mo		11	154
user1	12.12.2017	Tu	visit	0	11
user1	12.12.2017	Tu		11	154
user1	13.12.2017	We	visit	0	8
user1	13.12.2017	We		8	88

I would be glad about any suggestions!

Many thanks in advance and best regards,

Claus

Re: data model or calculation problem?

petter — Tue, 27 Mar 2018 14:22:07 GMT

I would think that you need to improve your data model as a preliminary conclusion based on what you tell us.

Is it possible for you to share the data model with us by taking a screenshot from the Table Viewer?

Re: data model or calculation problem?

Anonymous — Tue, 27 Mar 2018 14:28:36 GMT

Hello Petter,

Many thanks for your fast response...

Here is a screenshot of the relevant tables.

ID is my user table and the activities and visits are stored in my fact table.

Many thanks and best regards,

Claus

Re: data model or calculation problem?

petter — Tue, 27 Mar 2018 15:58:01 GMT

Could you elaborate on how the concatenated FACTS table has been created? Is visit a kind of activity - how are they defined?

Re: data model or calculation problem?

Anonymous — Wed, 28 Mar 2018 06:53:33 GMT

Visits and Activities are from different sources and only linked by date or User ID.

The different sources are concatenated loaded into the fact table.

When I export the fact table, I get for each day, user and fact type line.

%ID_ID	date	activity (B2)	visit activity (MA)	FACTTYPE
100007622	01.01.2018	holiday		B2
100007622	02.01.2018	holiday		B2
100007622	03.01.2018	visit		B2
100007622	03.01.2018		visit	MA
100007622	04.01.2018	visit		B2
100007622	04.01.2018		visit	MA
100007622	05.01.2018	visit		B2
100007622	05.01.2018		visit	MA
100007622	08.01.2018	visit		B2
100007622	08.01.2018		visit	MA
100007622	09.01.2018	visit		B2
100007622	09.01.2018		visit	MA
100007622	10.01.2018	visit		B2
100007622	10.01.2018		phone	MA
100007622	11.01.2018	visit		B2
100007622	11.01.2018		e-mail	MA
100007622	12.01.2018	visit		B2
100007622	12.01.2018		visit	MA
100007622	15.01.2018	visit		B2
100007622	15.01.2018		visit	MA
100007622	16.01.2018	visit		B2
100007622	16.01.2018		e-mail	MA
100007622	17.01.2018	visit		B2
100007622	17.01.2018		e-mail	MA
100007622	18.01.2018	visit		B2
100007622	18.01.2018		visit	MA

Re: data model or calculation problem?

Anonymous — Tue, 10 Apr 2018 14:12:36 GMT

I solved this now by moving the activity information from the dimensions into expressions.

E.g. =only(activity)

Now I get a unique line for each user and day.

Best regards,

Claus