topic Expression in pivot table. in QlikView

Expression in pivot table.

Sun, 17 Jul 2011 07:42:44 GMT

Hi,

I have transactions table in data model:

Year	Value
2007	1
2007	2
2009	3
2010	4

I build pivot table with one dimension(Year):

Year	Sum of Value
2007	3
2009	3
2010	4

I need to add new expression column where will be calculated sum of value of all transactions from transaction table till the end of the dimension column year:

Year	Sum of Value	Sum all Values till end of year
2007	3	3
2009	3	6
2010	4	10

I need to find the solution how build the expression(set analysis) to calculate this field. (I don't want to use the aggregated sums).

I tried something like that:

Sum(ALL{$<Year={"<=$([=Year])"}>} [Value])

but it doesn't work. Could anyone help me in my completly first step in QV?

Expression in pivot table.

Sun, 17 Jul 2011 08:04:03 GMT

Hi,

You can try something like that

if(RowNo()=1,[sum of value],[sum of value]+Above(some all values))

Expression in pivot table.

Sun, 17 Jul 2011 08:27:11 GMT

Thank you for your help .

It is the good solution, but based on the aggregated sums of columns above in pivot table.

I want to avoid the calculations based on shown columns (it can be not fixed). I want to base my calculation on all data in data table, if it is possible.

Re: Expression in pivot table.

Miguel_Angel_Baeyens — Sun, 17 Jul 2011 10:06:24 GMT

Hi,

Are you using a straight table? If yes, go to the Expressions tab in the chart Properties, click on the expression and tick the "Full Accumulation" check at the bottom of the dialog.

If you are using a pivot table instead, unless you allow the users to pivot the dimensions and expressions, the following should work

RangeSum(Above(Sum(Value), 0, RowNo()))

The Above() refers to the position of the dimension to aggregate, not to the column name, so that expression will work and return the same as the "Full Accumulation" mentioned above.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Expression in pivot table.

Sun, 17 Jul 2011 14:07:16 GMT

Thanks a lot for your help, but this method of calculation gives me results which depend from views and filters. I need to have contant results calculated only from transaction table.

Example situation:

When I click on row with 2010 I should have result:

Year	Sum of Value	Sum all Values till end of year
2010	3	10

but when I use your accumulation method I have:

Year	Sum of Value	Sum all Values till end of year
2010	3	4

which is wrong value.

Probably I should prepare this calculation during loading the script, but I'm looking for the solution to omit this.