Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Expression in pivot table.

Hi,

I have transactions table in data model:

Year
Value
20071
20072
20093
20104

I build pivot table with one dimension(Year):

Year
Sum of Value
20073
20093
20104

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
200733
200936
2010410

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?

4 Replies
Not applicable
Author

Hi,

You can try something like that

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

Not applicable
Author

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.

Miguel_Angel_Baeyens

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

Not applicable
Author

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
2010310

but when I use your accumulation method I have:

Year
Sum of Value
Sum all Values till end of year
201034

which is wrong value.

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