Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Hi,
You can try something like that
if(RowNo()=1,[sum of value],[sum of value]+Above(some all values))
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.
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.
BI Consultant
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.