Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

- Qlik Community
- :
- Forums
- :
- Analytics
- :
- New to Qlik Sense
- :
- Re: sum of column

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

leroboy1980

Contributor

2018-01-23
02:09 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

sum of column

hello,

I have a formula to make certain values 0 => if(Afdeling='Snijden',if(left(zustandname,6)='ReProd','0',Sum(([A.ZEIT]/A.STUECK)*(A.STUECK-A.FERTIG)))).

If I make the sum of this column he allso counts the values which are 0.

how can I solve this problem?

THX

844 Views

5 Replies

petter

Partner - Champion III

2018-01-23
02:56 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

The NULL will never be counted. The Count()-function will just skip the nulls and that goes for other aggregation functions as well along with the RangeXXX-functions too. In expressions outside of aggregations it will often prevent a numeric result.

The null can be produced by the **Null()** function and an If() function lacking a third parameter will return NULL if the If() function evaluates to false.

Maybe this will produce the right result for you:

if(Afdeling='Snijden',if(left(zustandname,6)='ReProd',**Null()**,Sum(([A.ZEIT]/A.STUECK)*(A.STUECK-A.FERTIG))))

Normally Afdeling and zustandname should also be wrapped in an aggregation function unless you are 100% sure that any of these fields never have more than one value at the row level in the table or chart you are using the expression in.

ericasense

Contributor III

2018-01-23
05:49 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Hi Kenny,

there are two ways to approach this - first is by substituting the 0 with null as suggested above, so that Qlik doesn't count it.

The other is to write the expression so that the Count evaluates to what you need in the total while returning the correct values in the column, that is, if you don't like the

You could take advantage of the dimensionality() function, which returns a 0 if the cell is a total, to return what you need and change the function.

=if(dimensionality() = 0, [function to return what you need to count], [function that returns what you need in the cells)

Erica

698 Views

agigliotti

Partner - Master III

2018-01-23
07:13 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

let's try using the below expression:

=Sum( if( Afdeling = 'Snijden',

if( left(zustandname,6) = 'ReProd', 0, ( ([A.ZEIT]/A.STUECK)*(A.STUECK-A.FERTIG) ) ), 0

)

)

698 Views

juraj_misina

Luminary Alumni

2018-01-23
07:40 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

I think the problem here is with the Totals function for that column. Go to table properties, select the Tijd measure, and in the Total function dropdown select "Sum", which will do a sum of rows on the Totals rows.

The reason why your expression returns a different result is that you expect is in the If statements and how does Qlik evaluate expressions on total rows. By default Qlik Sense evaluates the expression on total row exactly as on each individual row, only ignoring dimension fields. So in your case, Qlik Sense cannot determine which individual Afdeling or zustandname value you mean, because on total row there are many values coming to consideration. Therefore on Total row Afdeling='Snijden' evaluates to false, as well as left(zustandname,6) = 'ReProd'. Hence result of your expression.

698 Views

leroboy1980

Contributor

2018-01-25
02:01 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

thank you all

698 Views