Aggregation with set analysis

[cpalbrecht]

I have the following base data table:

Date	Col1	Col2	Col3
Date1	1	A	-
Date2	-	A	-
Date21	-	Q	-
Date3	-	A	X
Date4	2	B	-
Date5	-	B	X
Date6	3	C	X
Date7	-	C	-
Date8	4	D	-
Date9	5	E	-
Date10	-	E	-
Date11	6	F	-
Date12	-	F	X
Date13	-	F	X
Date131	-	P	X
Date14	-	F	-

Now I want to count the text values at Col3 based on aggregation of Col2. Thw result should be the following table:

Date	Col1	Col2	newCol
Date1	1	A	1
Date4	2	B	1
Date6	3	C	1
Date8	4	D	0
Date9	5	E	0
Date11	6	F	2

To get Date, Col1 and Col2 I can simply filter Date, where Col1 is not null. Or I use directly a filter for Col1. But how can I count the text values at Col3, when I filtered the Date before? I thought of something with set analysis.

Does anyone have an idea?

BR

Nachricht geändert durch Christoph Albrecht

[jonathandienst]

How do you get a result in newCol of 2 for Date11?

[cpalbrecht]

The number of all text values of Col3 for value F of Col2.

[jonathandienst]

Calculated dimension

=Aggr(If(Len(Col1) > 0, Date), Date)

Expression for newCol

=Count(Distinct Col3)

[jonathandienst]

>The number of all text values of Col3 for value F of Col2.

Do you mean this?

• Get the Date values where Col1 is not null
• Get the value of Col2 for each Date value
• Count the total of that Col2 value for all Dates when Col3 is not null

[cpalbrecht]

Yes, exactly

[jonathandienst · Accepted solution]

Dimension

=Aggr(If(Len(Col1) > 0, Date), Date)

Expression/Measure

=Sum(Aggr(Count(TOTAL <Col2> Col3), Col2))

(image is QV, but it should work the same in Sense)

[cpalbrecht]

Thanks for your proposal. For me it is not working and I dont know why.

I also dont understand, why you use Aggr function to filter the dates? I used the dollowing filter:

If(not IsNull(Col1), Date) or If(Len(Col1) > 0, Date)

Nevertheless both variants are not working for me.

I changed the base data table a little bit. I added two more rows (Date21 and Date 131). As you can see, it could also be, that there are addional entries for Col3 -> Date131. But they have a different value of Col2. So they should not be counted.

[jonathandienst]

Either way will work, I just prefer the Aggr(). You could simplify your expression to remove some redundancy

=If(Len(Col1) > 0, Date)

(null values have a Len() of zero)