topic Re: How to Exclude Fields that has Zero Values? in QlikView

How to Exclude Fields that has Zero Values?

Wed, 06 May 2015 08:49:42 GMT

Hi All,

I Have a problem i've been trying to do it, but failed, how do i hide field values that has Zero Values like below:

Names	Month	Customer	Total Visits	Unique Visits
Leslie	Jan	Lebo	349	1
John	Feb	Lebo	90	1
John	Mar	Lebo	0	1
Jonas	Mar	Jacob	39	1
Jonas	Apr	Mike	0	1

Ones that are Highlighted in Red.

NB: Total Visits And Unique Visits are My Expressions.

If(RowNo()=0, NoOfRows(TOTAL), Count(DISTINCT Names)) // this is for Unique Visits

=sum(aggr(count(DISTINCT SalesDate),Customer,SalesDate)) // Used for Total Visits

Regards.

Re: How to Exclude Fields that has Zero Values?

pokassov — Wed, 06 May 2015 08:57:14 GMT

Hello!

For Unique Visits:

if([Total Visits]>0,sum(aggr(count(DISTINCT SalesDate),Customer,SalesDate)) ,0)

Re: How to Exclude Fields that has Zero Values?

Wed, 06 May 2015 09:02:00 GMT

Hi Thabiso,

the 0 is only showing for you due to your 'unique visits' expression, I'm not sure how you can have a unique visit value but not at least the same value in your totals visits to be honest?

it points to your unique visits expression not really giving the right result. If you change that expression, those rows should drop out for you.

Even if you have to make it relative to the other expression

If([Total Visits]>=1,

If(RowNo()=0, NoOfRows(TOTAL), Count(DISTINCT Names))

)

hope that helps

Joe

Re: How to Exclude Fields that has Zero Values?

Wed, 06 May 2015 09:05:06 GMT

Hi,

I works but, now my Unique Values are the same as Total Visits as of Unique Values should remain as 1 for all the rows.

Regards

Re: How to Exclude Fields that has Zero Values?

Wed, 06 May 2015 09:41:56 GMT

HI Joe,

the code works, but im getting wrong answer for total sum

e.g

Customer	Total Visits	Unique Visits
	7	1
Lebo	2	1
Lebo	5	1

the answer for Unique Visits remains as 1 it doesn't give me the Total sum of those Unique Values.

i used Straight Table, and if i use Pivot the values changes and they become more, the Answer becomes 3 on Unique Visits.

Regards.

Re: How to Exclude Fields that has Zero Values?

Wed, 06 May 2015 10:28:41 GMT

if you are using a straight table, you could use the 'sum of rows' total option for that expression.

Looking at what you are trying to do though, I would say you are missing some fact fields which would make this much easier for you. Having to use Aggr like that for your visit count, if you had a visit count field in your underlying model, that expression becomes simpler.

If you have an example to upload that would be good, as I am fairly sure changes can be made to the model to make this easier for you

Joe