topic Re: Expression Total not calculating in QlikView

Expression Total not calculating

Anonymous — Tue, 25 Nov 2014 21:33:15 GMT

I have a straight table with 3 columns: Gross Revenue; COGS; and GP%.

The Total Mode for the first 2 columns is set to Sum, and is calculating the correct figures. The Total Mode for GP% is set to Expression Total, which is returning '-'.

Gross Revenue	COGS	GP%
$405.00	$139.35	65.59%
$87.27	$30.96	64.53%
$3,545.82	$1,531.50	56.81%
$18.00	$6.27	65.17%
$13.64	$4.70	65.53%
$659.45	$273.43	58.54%
$4,729.18	$1,986.21	-

The GP% expression is '= 1 - ([COGS] / [Gross Revenue])'. I am referencing the label names of the first 2 columns in this formula, could that be the reason it is not calculating?

Re: Expression Total not calculating

jaimeaguilar — Tue, 25 Nov 2014 22:09:15 GMT

Hi,

I think the problem here is that your columns in the straight table are called exactly as your fields, so when you're doing the calculation in the GP% column, QlikView is taking the name of the fields instead of the columns. Because of this, the expression totals appears as null ( - ), because internally qlikview is taking your formula as "1 - (only([COGS])/ only([Gross Revenue]))".

. Try changing the name of the columns in the straight table. You can add a blank space after the last letter so the names will be different and then you can call the column names like this:

= 1 - ([COGS ] / [Gross Revenue ])'

Also as a tip you can differentiate an object column from a field looking at the color it has when typing the expression (columns are shown in blue, while field names are shown in dark red),

regards

Re: Expression Total not calculating

Anonymous — Tue, 25 Nov 2014 22:20:02 GMT

Jaime, thank you for your response. Unfortunately this is not the problem, as I do not have fields for COGS and Gross Revenue, they are calculated from the fields CostPerUnit * Qty and PricePerUnit * Qty.

I have tried moving all my formulas into variables but this results in the same problem. My next step will be to edit the script to create the fields COGS and Gross Revenue, calculating them during the load.

Re: Expression Total not calculating

Anonymous — Wed, 26 Nov 2014 00:27:25 GMT

I have found by calculating COGS and Gross Revenue in the load script, rather than in the straight table, the GP% total calculates.