topic Re: Calculating the 4th quarter data in QlikView

Calculating the 4th quarter data

Wed, 16 Apr 2014 13:49:13 GMT

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

Re: Calculating the 4th quarter data

Anonymous — Wed, 16 Apr 2014 14:12:30 GMT

From your description, it looks like it should be

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

Re: Calculating the 4th quarter data

Wed, 16 Apr 2014 15:01:12 GMT

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

Re: Calculating the 4th quarter data

Wed, 16 Apr 2014 15:36:09 GMT

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

Re: Calculating the 4th quarter data

Anonymous — Wed, 16 Apr 2014 16:16:56 GMT

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.