Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

cpalbrecht
Contributor

Aggregation with set analysis

I have the following base data table:

DateCol1Col2Col3
Date11A-
Date2-A-
Date21-Q-
Date3-AX
Date42B-
Date5-BX
Date63CX
Date7-C-
Date84D-
Date95E-
Date10-E-
Date116F-
Date12-FX
Date13-FX
Date131-PX
Date14-F-

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

DateCol1Col2newCol
Date11A1
Date42B1
Date63C1
Date84D0
Date95E0
Date116F2

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Aggregation with set analysis

Dimension

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

Expression/Measure

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

Capture.PNG

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

8 Replies
MVP
MVP

Re: Aggregation with set analysis

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

cpalbrecht
Contributor

Re: Aggregation with set analysis

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

MVP
MVP

Re: Aggregation with set analysis

Calculated dimension

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

Expression for newCol

=Count(Distinct Col3)

MVP
MVP

Re: Aggregation with set analysis

>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
Contributor

Re: Aggregation with set analysis

Yes, exactly

MVP
MVP

Re: Aggregation with set analysis

Dimension

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

Expression/Measure

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

Capture.PNG

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

cpalbrecht
Contributor

Re: Aggregation with set analysis

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.

MVP
MVP

Re: Aggregation with set analysis

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)

Community Browser