Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
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
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

Highlighted
Contributor III
Contributor III

Re: aggregation

Thank you so much swuehl