topic Re: Show Only rows greater than zero in pivot table in QlikView

Show Only rows greater than zero in pivot table

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Dealer

Name

Region

State

City

Channel

Competitor

Vol

Val

SEC Count

ANDD000084

SANGEETHA MOBILES PVT LTD

SOUTH 2

ANDHRA PRADESH

KURNOOL

OPPO

20400

ANDD000084

SANGEETHA MOBILES PVT LTD

SOUTH 2

ANDHRA PRADESH

KURNOOL

OTHERS

6200

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))))

Re: Show Only rows greater than zero in pivot table

vishsaggi — Mon, 19 Jun 2017 11:42:22 GMT

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.

Re: Show Only rows greater than zero in pivot table

sunny_talwar — Mon, 19 Jun 2017 11:42:24 GMT

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

Re: Show Only rows greater than zero in pivot table

Anonymous — Mon, 19 Jun 2017 11:51:11 GMT

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.

Re: Show Only rows greater than zero in pivot table

sunny_talwar — Mon, 19 Jun 2017 11:55:09 GMT

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))

Re: Show Only rows greater than zero in pivot table

Anonymous — Tue, 20 Jun 2017 05:34:56 GMT

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))))

Re: Show Only rows greater than zero in pivot table

Anil_Babu_Samineni — Tue, 20 Jun 2017 05:41:05 GMT

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

Re: Show Only rows greater than zero in pivot table

sunny_talwar — Tue, 20 Jun 2017 08:53:10 GMT

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?

Re: Show Only rows greater than zero in pivot table

Anonymous — Tue, 20 Jun 2017 08:57:21 GMT

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())