Set Analysis - Compare dates in different tables

[RSltz]

Hello,

I have two tables, each contains time data. The first table contains the assignment of an employee to an entity/division as organization info. This assignment may change with time, so I have a calendar and the assignment is done for each month.

Month            employee  division
Jan 2020        A. Alpha      Div1
Feb 2020        A. Alpha      Div1
Mar2020        A.Alpha       Div2
Jan 2020        B.Beta         Div2
Feb2020        B.Beta         Div2
Mar2020        B.Beta         Div2
Jan2020         C.Gamma   Div2
Feb2020        C.Gamma   Div1
Mar2020        C.Gamma   Div1

The second table consists info about e.g. documents, it beside further information (title, id) it contains info about the autor (=employee) and a creation date.

DOC_ID                 Creation date     employee
1                              15.01.2020           A.Alpha
2                              20.02.2020           B.Beta
3                              05.03.2020           B.Beta
4                              10.03.2020           A.Alpha

So both tables are connected via the field employee.

I want to visualize, how many documents are generated per time interval by each division. For instance a pivot table, using the month as columns and the division as rows

                Jan         Feb        Mar
DIV1      1              -              -
DIV2      -              1              2

So I need an expression like

Count(Aggr(DOC_ID, division, Month))

But I have to include a set Analysis, that looks for the division to which the autor(employee) belongs , when the document was created. Something like

Aggr({<Month={“=$(=Month(Creation Date))”}>} Division, Employee)

Finally both expressions have to be combined somehow:
Count(Aggr({<Month={“=$(=Month(Creation Date))”}>} DOC_ID,Division, Employee) ???????

I really would be glad about any suggestion.
Thanks a lot

Do you have any suggestions ??

[canerkan]

Hi RSltz,

try building a generic key like this:

Assignment: 

Load

Month & '_' & employee as %Key

Month as Assign.Month,

employee as Assign.employee,

division as Assign.division

From ..... ;

Documents:

Load

Monthname(Creation date) & '_' & employee as %Key,

DOC_ID as Doc.DOC_IDm

Creation date as Doc.CreationDate,

employee as Doc.employee

From.....;

an then just use Count(Doc.DOC_ID) in your table.

I hope this helps,

Can

[RSltz]

Hello Can,

thank you very much for your answer. This seems to work pretty well, the assignment of dokument to a division follows the change of an employee.  I think, this already helps me a lot.

But now it looks as if the entries for each document have been doubled.
If i create a combined table for document and division info ,  i see two rows for each document.

Dok.ID   Dok.CreationDate  Dok.Employee  Assign.Division    Assign.Month
01           dd.01.yyyy                 A.Dam                  Division_A              Jan YYYY
01           dd.01.yyyy                 A.Dam                  Null() -                      Null() -
02           dd.02.yyyy                 A.Dam                  Division_B              Feb YYYY
02           dd.02.yyyy                 A.Dam                 Null()-                        Null() -

A Pivot table also shows the double entries:

                       Null()         Jan.          Feb
Null()           2                   -                  - 
Division A   -                   1                 -
Division B  -                    -                 1

Is there a way to avoid this ?

Thanks & Best regards Rainer

[canerkan]

Hi Rainer,

sorry for my late reply. Do you have a sample .qvf that you can provide by chance? Or maybe check your field '%Key' in both tables for any differences in format or values that might cause an invalid connection between these tables.

There could be a problem if an employee changed the Division within a month. But i need sample data to check that.

Regards, Can