topic reference the total in a column in QlikView

reference the total in a column

Mon, 02 Aug 2010 19:38:54 GMT

Hello,

I am working with a straight table which contains a couple of dimensions and a few simple expressions. I would lile to use the total of one column ( [Sales] ) in another calculation, but doing

sum( TOTAL [Sales]) doesn't work

The [Sales] expression is a calculated field itself, not a field loaded in script.

Any ideas why that doesn't work?

Thanks

reference the total in a column

Mon, 02 Aug 2010 20:32:16 GMT

You said Sales was not a field from the script, but the way you are using it assumes that it is.

Instead take whatever expression you are using for Sales and use that with the TOTAL parameter. We would need to see your Sales expression to give more specific information.

reference the total in a column

Mon, 02 Aug 2010 20:51:55 GMT

That's exactly what I also thought of doing, but the Sales calculation is build from other calculations that were also built upon...so I don't want to replicate the whole logic again. Some of them contain If Else statements etc.

ex:

[Sales] is [Bus Sales] + [Res Sales]

I created [Bus Sales] by adding 2 other columns: [ B Acc Value] + [B Act Value]

[B Acc Value] is created by doing If ( [B Type Value] ='record', 0, 1)

etc..

Any thoughts?

reference the total in a column

Tue, 03 Aug 2010 20:16:16 GMT

You could try:

Sum(TOTAL [Bus Sales] + [Res Sales])

Is there an aggregation in Bus Sales and Res Sales?

You don't have to go all the way back and replicate the entire logic structure. Find the first agregation going back from Sales and then use that with TOTAL to get back to your Sales.

reference the total in a column

Wed, 16 Mar 2011 18:43:56 GMT

I am having the same issue. I tried the suggestion of adding total, but I am receiving a null value. Any other suggestions?

reference the total in a column

Oleg_Troyansky — Wed, 16 Mar 2011 21:40:17 GMT

Kristy and Maria,

TOTAL will only work inside an aggregation function that's applied to a field or a calculation based on fields (but not variables that contain other functions inside).

If you have more than one field that you need to summarize, I recommend using function RANGESUM(), to avoid problems with nulls. For example:

If you have a formula like this:

sum(Revenue1 + Revenue2)

If one of the two numbers can be null() or missing, the sum will also be equal to null(). So, if Revenue2=null(), then:

Revenue1+Revenue2 = null()

, however:

RANGESUM(Revenue1, Revenue2) = Revenue1, because function RANGESUM() is replacing nulls() with zeros.

So, back to your original question about total, - the following formula, as an example, should work:

sum(TOTAL RANGESUM(Revenue1, Revenue2))

cheers,