Show Only rows greater than zero in pivot table

[Anonymous]

Dealer	Name	Region	State	City	Channel	Competitor	Vol	Val	SEC Count
ANDD000084	SANGEETHA MOBILES PVT LTD	SOUTH 2	ANDHRA PRADESH	KURNOOL	MT	OPPO	2	20400	1
ANDD000084	SANGEETHA MOBILES PVT LTD	SOUTH 2	ANDHRA PRADESH	KURNOOL	MT	OTHERS	1	6200	0

Hi all ,

I need to show record of a dealer only where SEC Count expression is greater than 0 .  No record should be shown for 0 sec count .

In Sec Count i m doing 2 things:

1)Sum({<CountershareType={'SecCountCompetitors'}>}UserResponse)

2)Count(DISTINCT SECCode)

Overall expression:

=if(Flag='Samsung',Count(DISTINCT SECCode), if(Flag='Competitors',Sum({<CountershareType={'SecCountCompetitors'}>}UserResponse), (sum({<CountershareType={'SecCountCompetitors'}>}UserResponse)+Count(DISTINCT SECCode))))

[vishsaggi · Accepted solution]

Your excel file is corrupted it seems, when downloaded, it doesn't open. However, why don't you use the expression in your calculated dimension like may be:

= Aggr( IF(

                   (

                       sum({< CountershareType={'SecCountCompetitors'}, Flag = {'Competitors'} >}UserResponse) +                        Count(DISTINCT {<Flag = {'Samsung'} >} SECCode)

                    ) > 0, Dealer), Dealer)

Have not tried, just check and let us know.

[sunny_talwar]

Do you just have one expression? Have you made sure that if you checked 'Suppress Zero Values' on the presentation tab of chart properties?

[Anonymous]

I have checked suppress zero value , it is not working .

I have 3 expressions :

1) Vol   :-  sum(Qty)+Sum({<CountershareType={'CounterShareVol'}>}UserResponse)

2) Val   :-  sum(DP)+Sum({<CountershareType={'CounterShareVal'}>}UserResponse)

3) Sec Count .  :-  

if(Flag='Samsung',Count(DISTINCT SECCode), if(Flag='Competitors',Sum({<CountershareType={'SecCountCompetitors'}>}UserResponse),

(sum({<CountershareType={'SecCountCompetitors'}>}UserResponse)+Count(DISTINCT SECCode))))

I want to show information of a dealer on basis of following

Sec Present/Absent (Yes/No)	Competitor	Samsung	Counter Sellout Volume	Counter Sellout Value (In INR)	SEC Count
	Yes	No	show	show	>0
	No	Yes	show	show	>0
	No	No	Don't show	Don't Show	0

If No Sec is present on both Competitor and  Samsung Dealer then i dont want to show record of that Dealer in report .  Irrespective of Vol/Val coming against that  Dealer.

Please help.

[sunny_talwar]

May be try something like this for your other two expressions

Assuming your SEC Count expression has the label "SEC Count", try this

1) Vol: If([SEC Count]) <> 0, sum(Qty)+Sum({<CountershareType={'CounterShareVol'}>}UserResponse))

2) Val: If([SEC Count]) <> 0, sum(DP)+Sum({<CountershareType={'CounterShareVal'}>}UserResponse))

[Anonymous]

Hi ,

I m getting the overall total(for all records)  in pivot table . But i need total on top only for those records(rows)  that is coming in table . I think  i m using Count(Distinct) + sum(user response)  that is not working properly .

Sec Count expression :

if(Flag='Samsung',Count(DISTINCT SECCode), if(Flag='Competitors',Sum({<CountershareType={'SecCountCompetitors'}>}UserResponse),

(sum({<CountershareType={'SecCountCompetitors'}>}UserResponse)+Count(DISTINCT SECCode))))

[Anil_Babu_Samineni]

Will you able to provide Screenshot or May be helpful if you provide QVW

[sunny_talwar]

I am not sure I follow your question... what exactly are you looking for? Is this a separate issue from above? or are we still trying to handle the same issue?

[Anonymous]

I used below expression as above and got the result .

Thanks

if(aggr(IF((count({<Flag={'Samsung'}>}DISTINCT SECCode)+Count({<Flag={'Competitors'},CountershareType={'SecCountCompetitors'}>}DISTINCT SECCode))>0,count({<Flag={'Samsung'}>}DISTINCT SECCode)+Sum({<CountershareType={'SecCountCompetitors'}>}UserResponse)),Store_Code)>0,Store_Code,null())