topic Re: filter zero values and adjust code in QlikView

filter zero values and adjust code

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

Good morning,

I ask for your help to solve two problems I can't solve.

1) The first is the "filter zero values". I would like to filter every row that has [money cashed by single game] = 0.

I have been looking to many posts and tried but could not find a solution

2) On a previous posts I got solutions from "swuehl" and "SunnyT." and I would like to see if Stefen's code can be adjusted to show the total number of games that are in one platform, like in Sunny's solution, or in my case I cannot use that code.

I show here below two pictures for the above two questions and I enclose the sample files.

Thank you for your help

Antonio

Re: filter zero values and adjust code

sunny_talwar — Fri, 08 Apr 2016 16:23:39 GMT

Try the attached

//based on a solution by Stefan Wuhl on March 31, 2016 from Qlikview forum

// ATTENTION: total on the top is not correct

=If([total cashed] > 0, Count({<[serial number]>}Total<Town,Centre,[platform name],[game name]> [serial number])*Avg(1))

//based on a solution by Stefan Wuhl on March 31, 2016 from Qlikview forum

// ATTENTION: total on the top is not correct

=If([total cashed by platform] > 0, Count({<[serial number]>}Total<Town,Centre,[platform name],[game name]> [serial number])*Avg(1))

Re: filter zero values and adjust code

Anonymous — Sat, 09 Apr 2016 14:00:17 GMT

Thank you Sunny !!! Works perfectely...

So, the If...on the expression "total cashed by the platform" at the beginning acts as a filter...!!!

Furthermore you modified the other one changing from ...*Sum(1)/* to ...*Avg(1)/*.

I am not familiar with these so please can you briefly comment what ...*Avg(1))/* does at the end of:

=If([total cashed by platform] > 0, Count({<[serial number]>}Total<Town,Centre,[platform name],[game name]> [serial number])*Avg(1))/*

Best regards

Antonio

Re: filter zero values and adjust code

sunny_talwar — Sun, 10 Apr 2016 12:08:02 GMT

So Stefan's intention was that to change my if statement at the beginning of my initial solution

If(Count([serial number]) > 0,

to be driven by a simple expression

Sum(1)

But for some unknown reason to me, Sum(1) was showing a value greater than 1 for each of your dimension and Stefan's goal was to make it multiply by just 1. So I changed Sum(1) to Avg(1)

The other If Statement is to do exactly what you meant. Based on the first expression being greater than 0, remove other expressions if they don't meet the criteria.

HTH

Best,

Sunny

Re: filter zero values and adjust code

Anonymous — Mon, 11 Apr 2016 07:03:00 GMT

Thank you Sunny,

Best regards

Antonio