0,1 should be selectable through list box

[Anonymous]

Hi ,

In pivot table for no data i need to show 0 instead of '-' symbol and also in list box need to show 0,1.

when i select 0 in list box need to show 0 vales in pivot table and for 1 selection also need to show 1 values in pivot table.

Thanks,

Yuvrajesh

[Anonymous]

The data is not null ,it is missing that's why i am not able use isnull() function

[Anonymous]

Yes correct adam..data not contains nulls ,data has missing values,due to that not able to replace with nulls.

Thanks,

Rajesh

[khalander]

Hi,

uncheck the option suppress zero values in presentation. and write expression like below

=if([Selection filter]=0,

if(Sum(sales)=0,0,sum(sales)),

if(Sum(sales)=0,1,sum(sales)))

[MK_QSL]

OK then change the expression as below

IF(SUM(sales)>0,1,0)

and you can't get the same in Pivot table.

You have to convert to straight table.

check enclosed file..

[Anonymous]

Hi I have checked Manish..i need to show month as row,means i need to select pivot table only..in stright table missing values won't be display when u put month as column.

Thanks,

Rajesh

[MK_QSL]

No way you can get the desired result in pivot table.

[khalander]

Hi Rajesh,

first create excel sheet with two fileds with Name and monthname and fill the data like

Name,monthname

Rajesh Jan

Rajesh Feb

.

.

Rajesh Dec

for all users.

Then use the below script

Name_mapping:

LOAD

     Name,

     monthname

FROM

[..\New Microsoft Excel Worksheet.xlsx]

(ooxml, embedded labels, table is Sheet1);

Join

LOAD * Inline 

[ 

  Name,monthname,sales 

  Rajesh,Jan,100 

  Rajesh,Feb,100 

  Rajesh,March,100 

  Rajesh,April,100 

  Rajesh,May,100 

  Rajesh,June,100 

  Rajesh,July,100 

  Rajesh,Aug,100 

  Rajesh,Sept,100 

  Rajesh,Oct,100 

  Rajesh,Nov,100 

  Rajesh,Dec,100 

  Pavan,Jan,200 

  Pavan,March,300 

  Pavan,Oct,400 

  Venu,Nov,300 

  Venu,Sept,200 

]; 

Data:

LOAD *,

Name&monthname as key

Resident Name_mapping;

LOAD

Name&monthname as key,

if(IsNull(sales)<0,0,1) as flag

Resident Name_mapping;

DROP Table Name_mapping;

Output:

[MK_QSL]

When you click on 1.. is that giving you all Name and monthname with flag 1 only?

[khalander]

yes Manish,

PFB screenshot

[MK_QSL]

This is exactly the same which I have provided.

Try to keep monthname as column and let me know whether it's working or not.