Calculating the 4th quarter data

[#1]

I've a combo chart which measures the opening balance and sales and losses for each quarter of the selected year

It does this by taking the opening balance position per quarter, applying the sales, looking at the open balance for the next quarter and then deducing the losses for the previous quarter. This works well apart from the 4th quarter where it can't see the opening balance of the next year

Opening balance is calculated

=sum({< MonthNameAbv = {'Jan','Apr','Jul','Oct'}>}Amount)

I was simply using the equation below to get losses

=

column(1)- below(column(1)) + column (2)

with column 1 = opening balance

column 2 = sales

but obviously when i run out of quarters I can't calculate so im trying to substitue the

below(column(1))

statement with something like the following :-

sum({<Year = {'$(=Addyears(Year,-1)'} ,  MonthNameAbv = {'Jan'}>}Amount))

so i can get the opening balance for the next year.

This isn't quite working and I think its a syntax error, can anyone help please

thank you

[Anonymous · Accepted solution]

From your description, it looks like it should be

sum({<Year={"$(=Year+1)"} ,  MonthNameAbv = {'Jan'}>}Amount))

[#3]

Thanks Michael that certainly got the answer i wanted simply adding the " ", so it worked perfect as a standalone

but when i substitued into the equation it ignored that line, so using the equation below

= if (Quarter = 4,

                sum({< MonthNameAbv = {'Jan','Apr','Jul','Oct'} >}Amount)

                -

                sum({<Year={"$(=Year+1)"} , MonthNameAbv = {'Jan'}>}Amount)

                +

                column (2)

    ,    column(1)- below(column(1)) + column (2))

it basically  did

column (1) + column (2)

do you know if the syntax above is incorrect

[#4]

I found that by putting the equation in the set analysis it just wouldn't work for whatever reasons however if I declared a new variable and used that equation defined by michael above in the definition of the variable, THEN used the variable in the set analysis equation

It works!!

but dont ask me why

[Anonymous]

It likely has to do with the object you use it in.  Variable is independent of any object properties, so it works.

The important part that it works now.