topic Re: Ad-hoc Pivot selection issues in QlikView

Ad-hoc Pivot selection issues

infock12 — Wed, 25 Nov 2015 15:35:27 GMT

Hi all,

I have an Ad-hoc report page with the following script.

AdHocDimensions:
LOAD * Inline [Dimension_1, dimension_flag
Site, 1
Direct, 2
Dept, 3
Func, 4
];

AdHocParameter:
LOAD * Inline [Parameter_1, Parameter_flag
Grid, A
];

The expression I am using is below:

=aggr(nodistinct if(count({<GRating = {'10'}>} GQuestions)>=3 and count({<Rating = {'<=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'))))), Func, Domain, Direct, Site, Dept)

This works when I have selected all the 4 adhoc dimensions in List Box, but when I unselect a dimension, I am getting null values. I tried removing 'nodistinct' but it doesn't help. Looks like something simple but not sure what. Any help is appreciated.

Thanks,

Karthik

Re: Ad-hoc Pivot selection issues

sunny_talwar — Wed, 25 Nov 2015 15:41:03 GMT

Can't seem to determine what might be the issue, may be if you can provide a sample to look at, it might become easy.

Re: Ad-hoc Pivot selection issues

Wed, 25 Nov 2015 15:43:11 GMT

can you provdie the qvw with data - Also I see the aggr function aggregating over the dimensions but also see 'Domain' in the aggr function?

Re: Ad-hoc Pivot selection issues

infock12 — Wed, 25 Nov 2015 15:55:14 GMT

Hi Sunny,

I will try and post a sample

Re: Ad-hoc Pivot selection issues

infock12 — Wed, 25 Nov 2015 15:59:14 GMT

Hi Adam,

Yes, I have not added Domain in the Inline script but I have added it for the pivot as a Dimension. However, I have NOT conditionally enabled (=SubStringCount(Concat(dimension_flag, '|'). In spite of that, it works when all the other dimensions are selected. The issue happens only when I un-select one of the dimensions in the pivot.

Re: Ad-hoc Pivot selection issues

infock12 — Wed, 25 Nov 2015 16:46:56 GMT

Hi Adam and Sunny,

Please find the qvw and data file attached. The expression I am using is:

=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'))))), Func, Domain, Direct, Site, Dept)

When I select all adhoc dimensions, everything works. When I unselect, for example, Direct, some of the fields in the pivot domain becomes null although those areas have data as you can see from the spreadsheet.

Re: Ad-hoc Pivot selection issues

Wed, 25 Nov 2015 16:55:27 GMT

I am not sure I understand. I can check either all of the dimensions or jsut 1 dimension and I get values based on what is selected.

I do get null values in some areas when jsut one item is slected but not all values are null

The only time the table is empty is when no eimensions are selected, and that is due to the conditon you have on the object

=GetSelectedCount(Dimension_1) > 0 AND GetSelectedCount(Parameter_1)>0

Re: Ad-hoc Pivot selection issues

infock12 — Wed, 25 Nov 2015 17:01:05 GMT

Hi Adam,

Please try selecting Site, Func, Direct under dimensions.

If you look at Site-ELIG, Direct-RBHT, and Func-SUPP, Blue and Purple are null.

Similarly, Site-MENP, Direct-RBHT, and Func-AND, Blue, Leader and Scarlett are null.

Re: Ad-hoc Pivot selection issues

Wed, 25 Nov 2015 18:38:41 GMT

I see what you ar talking about and I think it is related to the issue of some of the items do not have data and becasue of the nulls, it cant aggregate.

A step in trouble shooting to see if this is indeed the case is instead of the words being in the chart, replace it with the actual count

I have had issues in the past with pivot tables where the more condesnsed it is , I dont always get the detail but when fully exapnded, I do

Re: Ad-hoc Pivot selection issues

Wed, 25 Nov 2015 18:43:57 GMT

if you look how the data appears in a straight table compare to the pivot, you can see that some data does not exist

neither chart shows data for Dept ORTP, leader, purple or scarlett

what about a catch all statement for inforamtion that does not exist?

Re: Ad-hoc Pivot selection issues

Wed, 25 Nov 2015 18:59:53 GMT

I would also suggest you palce these in order from best to worse and make sure you do not overlap

there are 2 requireed improvements and several criteria that are <=. >= 3 and your counts can only meet one of the criteria. Butting them in order will help, and add a catch all that if it is not met put 'Not Applicable' or somethin similar