Ad-hoc Pivot selection issues

[infock12]

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

[sunny_talwar]

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.

[#3]

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?

[infock12]

Hi Sunny,

I will try and post a sample

[infock12]

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.

[infock12]

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.

[#7]

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

[infock12]

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.

[#9]

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

[#10]

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?