Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
apoorvasrivasta
Contributor III
Contributor III

Show Only rows greater than zero in pivot table

DealerNameRegionStateCityChannelCompetitorVolValSEC Count
ANDD000084
SANGEETHA MOBILES PVT LTD
SOUTH 2
ANDHRA PRADESH
KURNOOL
MT
OPPO
2
20400
1
ANDD000084
SANGEETHA MOBILES PVT LTD
SOUTH 2ANDHRA PRADESHKURNOOLMT
OTHERS
162000

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

1 Solution

Accepted Solutions
vishsaggi
Champion III
Champion III

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.

View solution in original post

8 Replies
vishsaggi
Champion III
Champion III

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?

apoorvasrivasta
Contributor III
Contributor III
Author

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)
CompetitorSamsungCounter Sellout VolumeCounter Sellout Value (In INR)SEC Count
YesNoshowshow>0
NoYesshowshow>0
NoNoDon't showDon't Show0

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

apoorvasrivasta
Contributor III
Contributor III
Author

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
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?

apoorvasrivasta
Contributor III
Contributor III
Author

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