If expression with dimension calculation

[#1]

Hi

I want to create an expression in a straight table where I want the first dimension row to make one summation and the rest of the rows making another sum where there is a condition to sum for the previous month. The dimension of the table is Month called Date.Month in the data model.

=if(Date.Month=vMonth,Sum(InventSum),Sum(InventMonthEnd))

In the If statement above I want the second Summation Sum(InventMonthEnd) to Sum according to the precious month in the dimension.

Sum(InventMonthEnd) for previous Month, Date.Month-1. I dont know how to write this to have it working correct in the expression.

Thanks for any help!

[#2]

Kindly post your data or qvw with restricted data.

[swuehl · Accepted solution]

You can use chart inter record functions like above() to evaluate an expression in the context of a previous dimension line:

above(Sum(InventMonthEnd))

[israrkhan]

hi,

have you tried RowNo() function.

=if(Rowno() =1 And Date.Month=vMonth,Sum(InventSum),Sum(InventMonthEnd))

[#5]

Jackpot this works perfect. I had to use the Before function since I had the dimension in the horizontal direction, but same logic and it works great.

[#6]

Dont know if this works since the above answer works fine but it is probably a helpful answer.

[#7]

The data model is way too complex and the calculation I want to make as well for me to post restricted data. That would take a lot of time to have it working correct.