topic Totaling Columns with "if" function in QlikView

Totaling Columns with "if" function

Wed, 26 Jan 2011 02:17:01 GMT

I have two columns; one contains alpha data the other numeric. I need to total all nymeric that have corresponding alpha data. (i.e do not total if there is no alph data).

What is the totaling expression?

Newby Paul

Totaling Columns with "if" function

johnw — Wed, 26 Jan 2011 02:23:48 GMT

Perhaps this?

sum(if(len(trim(AlphaField)),NumericField))

This might be more efficient, though:

sum({<AlphaField*={">' '"}>} NumericField)

Totaling Columns with "if" function

Fri, 28 Jan 2011 00:18:40 GMT

John, thanks for your reply: Seems like I still can't get the right result. What I am trying to accomplish is to be able to take a list of sales quotes that wre made during the year and to be able to only total the dollar amount of those that were actually made into "jobs" ( Quote was accepted by customer) The data contains a field with the $$ of each quote and a field that is either blank ( not sold) or had a "job no" in it, meaning the quote was accepted. Just want to add up all those quotes that have and associated Job No.

" />

Newby Paul

Totaling Columns with "if" function

johnw — Fri, 04 Feb 2011 01:10:54 GMT

Sorry for the delay. I've been very busy. In the attached example, the first expression works fine, and the second expression doesn't. A slightly different set analysis expression works, though:

sum({Job-={''}>} Quote)

If none of this works for you, perhaps you can modify and repost the example to demonstrate the problem you're having.

Totaling Columns with "if" function

Fri, 04 Feb 2011 02:04:48 GMT

Thanks John...I will try that