My data look like this:
Global_ID | Local_ID | Ref | Type | Label | nT1 | nT2 | nT3 |
20160815 | 913514 | - | 1 | Foo | 1 | 1 | 1 |
20160815 | 913515 | - | 1 | Bar | 1 | 1 | 1 |
20160815 | 913516 | - | 1 | Bar | 1 | 1 | 1 |
20160815 | 913517 | 913514 | 2 | - | | | |
20160815 | 913518 | 913515 | 2 | - | | | |
20160815 | 913519 | 913516 | 2 | - | | | |
20160815 | 913520 | 913517 | 3 | - | | | |
20160815 | 913521 | 913518 | 3 | - | | | |
20160815 | 913522 | 913519 | 3 | - | | | |
20160816 | 12345 | | | | | | |
There is a variable number of type=1 per Global_ID. Each T=1 can have a number of T=2, which again holds a couple of T=3. Variables nT1 to nT3 is what I want to achieve in order to pivot all data (total number and average of type=2 and 3 per Label). When I aggregate now per Aggr(Sum(if([Type]=2,1,0)),[Ref]) these values are not written into the line with the label. Any idea how to do this "cross aggregation"? (Didn't find a better caption)