topic Aggregation over multiple fields/columns in App Development

Aggregation over multiple fields/columns

Robert089 — Thu, 22 Apr 2021 09:50:34 GMT

Hi, I'm really new in Qlik so far but the most thinks are working with my basic understanding.

At the moment I have follwing issue:

I have several products with different timestamps. Each Product has a product number and a unique serial number and a time stamp.

Now I want to make following analysis.

1. Which serial number has a timestamp in the last 12 month

This one is easy, I added a calculated field in the table of data manager

2. Which serial number has more or equal than 2 timestamps in the last 12 month

This aggregation works for me as I did for example a table in the analysis section and did the calculation with sum(calculated field)

3. Now I want to find out which product number has a serial number which has >=2 time stamps in the last 12 month

This aggregation doesn't work at the momemt as I have the issue, that I not know how I can aggregate the "sum(calculated field)" which is shown as a result in a table (as described in 2.)
My first intention was to make an aggregation of the table I created in step 2. But that not worked yet. (Is it possible in general)?
My second try was to build a new tabel in data manager with aggregated serail numbers in the last 12 month. But I couldn't find out how or if this is working

Do you have an idea how I can solve my issue how to aggregate over two fields?

Re: Aggregation over multiple fields/columns

GaryGiles — Thu, 22 Apr 2021 11:56:41 GMT

I believe that you can answer all three of these questions using set analysis in a table chart, instead of creating new fields. For your dimension in a table chart, use the following expressions. Results below.

Question 1: =aggr(Only({$<Timestamp={">$(=Addmonths(Today(),-12))"}>}[Serial Number]), [Serial Number])

Question 2:

=aggr(Only({$<[Serial Number]={"=Count({$<Timestamp={"">$(=Addmonths(Today(),-12))""}>} Timestamp)=2"}>}[Serial Number]), [Serial Number])

Question 3:

=aggr(Only({$<[Serial Number]={"=Count({$<Timestamp={"">$(=Addmonths(Today(),-12))""}>} Timestamp)>2"}>}[Product Number]), [Product Number])

Re: Aggregation over multiple fields/columns

Robert089 — Fri, 23 Apr 2021 06:56:35 GMT

Thanks, that helped me a lot!

Re: Aggregation over multiple fields/columns

Robert089 — Mon, 17 May 2021 10:35:19 GMT

I tried to finally get a clue to solve my issue with the aggr described above. As a final result I'm going to expect a table in QLIK like following:

Product numer | Number of Serial Number with more than 2 timestamps in the last 12 month

1234567 | 1

45378923 | 0

7437372 | 0

3452354 | 0

4353454 | 0

How should the formula look like for "Number of Serialnumbers with more than 2 timestamps in de last 12 month"