Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

aggregation

Hi Experts,

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

Sample Input data in 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 the one in the attached file

 

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

For a chart expression solution, you can try something like this

2019-10-28 11_33_09-QlikView x64 - [E__Users_Stefan_Documents_comm1638908.qvw_].png

 

With expressions:

Single:

=Sum( Aggr(If(Count({<[Product Details] -= {'N/A'}>} [Product Details])=1,1), Date, [15 Min Intervals], [Product No]))

Double:

Sum( Aggr(If(Count({<[Product Details] -= {'N/A'}>} [Product Details])=2,1), Date, [15 Min Intervals], [Product No]))

Triple:

Sum( Aggr(If(Count({<[Product Details] -= {'N/A'}>} [Product Details])=3,1), Date, [15 Min Intervals], [Product No]))

View solution in original post

3 Replies
Highlighted
Contributor III
Contributor III

Re: aggregation

someone please help me with the expression

Highlighted
MVP
MVP

Re: aggregation

For a chart expression solution, you can try something like this

2019-10-28 11_33_09-QlikView x64 - [E__Users_Stefan_Documents_comm1638908.qvw_].png

 

With expressions:

Single:

=Sum( Aggr(If(Count({<[Product Details] -= {'N/A'}>} [Product Details])=1,1), Date, [15 Min Intervals], [Product No]))

Double:

Sum( Aggr(If(Count({<[Product Details] -= {'N/A'}>} [Product Details])=2,1), Date, [15 Min Intervals], [Product No]))

Triple:

Sum( Aggr(If(Count({<[Product Details] -= {'N/A'}>} [Product Details])=3,1), Date, [15 Min Intervals], [Product No]))

View solution in original post

Contributor III
Contributor III

Re: aggregation

Thank you so much swuehl