Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Multiple Fact Tables

Hi everyone,

I've got a document with 4 facts table. There are dependent from each others.

Table 1 = Requests.

Each request can generate one or more Trip (Table 2).

A trip can be comosed of one or more services (Table 3) (but services can also exist without being linked to a trip neither to a request).

A Service can have one or more assignment.

I've already managed to make a single fact table but I don't know how to manage each mesures.

My Full fact table seems like

Request ID     Request Date     Trip ID     Trip Date           Service ID      Service Date     Assign.ID       Assign Date      Measure Service

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

1                    01/01/2012          1          02/01/2012          1                 02/01/2012       1                    02/01/2012          10

1                    01/01/2012          1          02/01/2012          2                 02/01/2012       2                    02/01/2012          20

1                    01/01/2012          1          02/01/2012          2                 02/01/2012       3                    02/01/2012          20

-                     -                         -          -                         3                 02/01/2012       4                    02/01/2012          30

2                    01/01/2012          2          02/01/2012          4                 02/01/2012       5                    02/01/2012          40

My question is for the request 1 - service 2.

If  i make sum on the service measure I wil have 50. What I want is 30 because 2 is the same service and it is duplicate because it is composed of 2 assignements but it should be counted only once. 

How do you manage that?

I think using Aggr but I don't really understand how it works and I could use some help.

Thanks

caro.

3 Replies
jason_michaelid
Honored Contributor II

Re: Multiple Fact Tables

Try:

Aggr(Sum([Measure Service]),[Request ID]) or you may need the DISTINCT qualifier in there:

Aggr(DISTINCT Sum([Measure Service]),[Request ID])

Hope this helps,

Jason

Not applicable

Re: Multiple Fact Tables

Hi Jason,

I don't really understand how the aggr works.

Could you explain it to me?

I know the distinct. Without the aggr(), I could use it for [Request ID] 1 and it would work but then if I don't select the request ID and I have 2 time the same measures for 2 different request ID, then I don't want the distinct on it.

Can Aggr solve this?

Thanks for your help

Caro.

Not applicable

Re: Multiple Fact Tables

I will explain it in my cowboy way

Aggr ignores dimensions. So if you want an expression to calulate using dimensions that aren't present in your table or you want to calculate something using only the first 2 dimensions in your report and not all 3, you would use aggr. Essentially you can build an entire straight table with no dimensions using aggr and still get the values you want. It is a resource intensive function, so use with caution on 'big' data sets

To answer your second question and using the above explanation

Aggr(DISTINCT Sum([Measure Service]),[Request ID],[Serivce ID]). Literally just add the dimension that has the logic you want to apply when summing the values you deem distinct

Cheers,

Byron

Community Browser