Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

Pivot and Aggr functions

Hi all,

Good afternoon! I have a two-dimensional pivot table with AGGR, IF AND COUNT expressions.

The two dimensions are Gsite and GFunction. The Finance 'Function' is located at two Sites (CRS, DLG) and I wanted to see the performance of 'Finance' at both the sites. I am using the following expression.

=aggr(nodistinct if(count({<GRating = {'10'}>} GQuestions)>=3 and count({<GRating = {'<=6'}>}GQuestions)=0, 'Outstanding',
if(count({<GRating = {'>=7 <=9'}>}GQuestions)=count(GQuestions), 'Good',
if(count({<GRating = {'<=3'}>}GQuestions)>=3, 'Inadequate',
if(count({<GRating = {'<=6'}>}GQuestions)>=3, 'Requires Improvement',
if(count({<GRating = {'<=3'}>}GQuestions)>=2, 'Requires Improvement', 'Good'))))), GFunction, GDomain)

When I DO NOT apply any filters, I am getting the same result for both the sites. Please see screen shot below. This is wrong because CRS and DLG sites have different values.

When I apply a filter CRS or DLG, the value changes. see screen shot below.

Could someone please tell me what is going wrong and how can I show the correct values for both the Sites without applying the filter?

I have attached the QVW file and the spreadsheet.

Thanks!

Karthik

1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

Hi,

Include the GSite in Aggregation.

=aggr(nodistinct if(count({<GRating = {'10'}>} GQuestions)>=3 and count({<GRating = {'<=6'}>}GQuestions)=0, 'Outstanding',

if(count({<GRating = {'>=7 <=9'}>}GQuestions)=count(GQuestions), 'Good',

if(count({<GRating = {'<=3'}>}GQuestions)>=3, 'Inadequate',

if(count({<GRating = {'<=6'}>}GQuestions)>=3, 'Requires Improvement',

if(count({<GRating = {'<=3'}>}GQuestions)>=2, 'Requires Improvement',  'Good'))))), GFunction, GDomain,GSite)

Regards,

Kaushik Solanki

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".

View solution in original post

2 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Hi,

Include the GSite in Aggregation.

=aggr(nodistinct if(count({<GRating = {'10'}>} GQuestions)>=3 and count({<GRating = {'<=6'}>}GQuestions)=0, 'Outstanding',

if(count({<GRating = {'>=7 <=9'}>}GQuestions)=count(GQuestions), 'Good',

if(count({<GRating = {'<=3'}>}GQuestions)>=3, 'Inadequate',

if(count({<GRating = {'<=6'}>}GQuestions)>=3, 'Requires Improvement',

if(count({<GRating = {'<=3'}>}GQuestions)>=2, 'Requires Improvement',  'Good'))))), GFunction, GDomain,GSite)

Regards,

Kaushik Solanki

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".

View solution in original post

Highlighted
Creator III
Creator III

Great, thanks Kaushik!