# New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
New Contributor III

## aggregation in set analysis

Hi Experts,

i have a table with Date, 15 Min Interval time, product no, ID & Name in my qvw.

Sample Input in the excel sheet attached

i have to provide a grouped result for a day at 15 min intervals like below. i have to find how many singles, doubles & triples in a day

But i have to exclude 'N/A' in the 'Product Details' field for my grouped result.

Each Product No will have multiple Product details associated with it. for example, in the above table, for 09/10/2019 at 12:00:00, there are 3 products with the same product no. so my result for 12:00:00 should be

09/10/2019        12:00:00                1 (Triple)

similarly, at 00:30:00, 3 products with product no SA711, but 2 of them shows N/A in product details. so i have to exclude that 2. my result at 00:30:00 should be

09/10/2019       00:30:00           1(Single)

so My entire output for the above table should be like:

Output:

 Date 15 Min Intervals Single Double Triple 09/10/2019 00:00:00 1 1 0 09/10/2019 00:15:00 1 1 0 09/10/2019 00:30:00 1 0 0 09/10/2019 01:15:00 0 1 0 09/10/2019 01:30:00 1 1 0 09/10/2019 01:45:00 0 1 1 09/10/2019 06:00:00 0 1 1 09/10/2019 06:15:00 0 1 1 09/10/2019 12:00:00 0 0 1

I have attached the input and output data in excel.

Labels (1)
• ### aggregation in set analysis

1 Solution

Accepted Solutions
Highlighted
MVP

## Re: aggregation in set analysis

Try the below expressions:

Single:

Count(if([Product Details]<>'N/A' and Aggr(count(if([Product Details]<>'N/A',[Product No])),Date,[15 Min Intervals],[Product No])=1,[Product No]))

Double:

Count(if([Product Details]<>'N/A' and Aggr(count(if([Product Details]<>'N/A',[Product No])),Date,[15 Min Intervals],[Product No])=2,[Product No]))

Triple:

Count(if([Product Details]<>'N/A' and Aggr(count(if([Product Details]<>'N/A',[Product No])),Date,[15 Min Intervals],[Product No])=3,[Product No]))

5 Replies
Highlighted
Partner

## Re: aggregation in set analysis

There you go:

Create your logic at script level:

Aggregate your table by [Product No], then create link key using [Date] and [15 Min Intervals].

Use the new count column from the aggregated table above at front end with set analysis to filter count by 1,2,3.

Thanks and regards,

Arthur Fong

Highlighted
New Contributor III

## Re: aggregation in set analysis

Hi Arthur,

but i have to bring the solution through expression.

i cannot do the changes in script as my fields are segregated in different tables.

Highlighted
MVP

## Re: aggregation in set analysis

Try the below expressions:

Single:

Count(if([Product Details]<>'N/A' and Aggr(count(if([Product Details]<>'N/A',[Product No])),Date,[15 Min Intervals],[Product No])=1,[Product No]))

Double:

Count(if([Product Details]<>'N/A' and Aggr(count(if([Product Details]<>'N/A',[Product No])),Date,[15 Min Intervals],[Product No])=2,[Product No]))

Triple:

Count(if([Product Details]<>'N/A' and Aggr(count(if([Product Details]<>'N/A',[Product No])),Date,[15 Min Intervals],[Product No])=3,[Product No]))

Highlighted
New Contributor III

## Re: aggregation in set analysis

thank you so much for your help arthur

Highlighted
New Contributor III

## Re: aggregation in set analysis

Thank you so much for your help tresesco