Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
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:

Date15 Min IntervalsSingleDoubleTriple
09/10/201900:00:00110
09/10/201900:15:00110
09/10/201900:30:00100
09/10/201901:15:00010
09/10/201901:30:00110
09/10/201901:45:00011
09/10/201906:00:00011
09/10/201906:15:00011
09/10/201912:00:00001

 

I have attached the input and output data in excel.

Can you please help me with the expression to get this result?

Labels (1)
1 Solution

Accepted Solutions
Highlighted
MVP
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]))

View solution in original post

5 Replies
Highlighted
Partner
Partner

Re: aggregation in set analysis

There you go:

MC.PNG

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

Re: aggregation in set analysis

Hi Arthur,

Thank you for your response.

but i have to bring the solution through expression.

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

 

can you please help me with an expression to get the result

Highlighted
MVP
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]))

View solution in original post

Highlighted
Contributor III
Contributor III

Re: aggregation in set analysis

thank you so much for your help arthur

Highlighted
Contributor III
Contributor III

Re: aggregation in set analysis

Thank you so much for your help tresesco