How to Show all value but Null in Pivot Table

[Anonymous]

Dear All

    I have a problem about report interface.

In my report I need to show EffectiveMonth with 24row and Monthdifferenct with 25 column.

There is no problem if I won't drill down to any selection.

But when I use the selection.My report show Null dimension in the last column like this.

The dimension name "No" is from Inline Load so I don't understand Where "Null" column came from. and How could I avoid it without skip some of EffectiveMonthYear and No 1-25

Please kindly advise.

Thank you

[sunny_talwar]

Would you be able to share a sample to take a look at this?

[Anonymous]

Hi Sunny

Thank you for your kind attention.

For data security I have to edit some sensitive data,Please give me some time.

[qliksus]

Have you modified your null value as "Missing"  in the presentation of the chart like this  and the reason could be that those months might not have any data for the selection you made

[Anonymous]

Dear Susant

   Yes,I have. I don't have any concern about any column but "Null" column on the right handside.
I would like to remove this excess column with out skip or surpress column "No" 1-25 and row "EffMonth".

Thank you

[qliksus]

The reason is there is some month which doesn't have the required value in field "No"  in your data . Consider the below example if you connect the data through "No" the april month don't have the data so when you take this in chart you will get the null value

LOAD *  INLINE [
    MonthYear, No
    Jan,1
    Feb,2
    Mar,3
    Apr,-
];

LOAD * INLINE [
   No
   1
   2
   3
    ] ;

You can filter the null values using expression like something below to get the result

If ( Isnull(No) ,0,sum(amount)

[sunny_talwar]

Sure thing... may be this thread can help you fasten things up

Preparing examples for Upload - Reduction and Data Scrambling

[Anonymous]

Dear Sunny

I already uploaded file.

Thank you for your very kind.

[Anonymous]

Dear Susan

   I have tried this solution you adviced but the output was the same.

Please kindly see my attached I'm not sure if I miss something with the expression.

Thank you so much.

[tresesco]

Yours seems to be a cross-table incomplete Cartesian product generated nulls/missing (you marked as 'Missing' in the chart), and that is why you don't see it/gets generated in the script. Please refer below the snippet from HIC's document: