# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Not applicable

## SET expression for compute the Balance sheet.

Hi All

Some time back my below expression will compute the BS figure for me , out of sudden this expression refuse to work :-

if([Exec P&L Level] = 's','',Sum({\$<year = {\$(=max(year))}, month = {"<=\$(=max({<year={\$(=max(year))}>} month))"}>}[Amount]*-1)/1000/\$(ColumnDim98))

In order to figure where go wrong , i need to know what are the above SET expression did ? Hope some one can share with me.

Paul

1 Solution

Accepted Solutions
Not applicable

## Re: SET expression for compute the Balance sheet.

Since the expression use <= for Month's set analysis it might be using Jan and Feb. (Although the current structure will only work if Month is in number because max() will output a number and you need to have same formats on the LHS and RHS of  set analysis).

if([Exec P&L Level] = 's','',Sum({\$<year = {\$(=max(year))}, month = {"<=\$(=max({<year={\$(=max(year))}>} month))"}>}[Amount]*-1)/1000/\$(ColumnDim98))

For getting just Feb, may be this:

if([Exec P&L Level] = 's','',Sum({\$<year = {\$(=max(year))}, month = {"\$(=max({<year={\$(=max(year))}>} month))"}>}[Amount]*-1)/1000/\$(ColumnDim98))

9 Replies
Not applicable

## Re: SET expression for compute the Balance sheet.

Hi,

Try like this

if([Exec P&L Level] = 's','',Sum({\$<year =, month =, DateFieldName {"<=\$(=max(DateFieldName))}>} [Amount]*-1)/1000/\$(ColumnDim98))

Hope this helsp you.

Regards,

Jagan.

Not applicable

## Re: SET expression for compute the Balance sheet.

Paul -

Set expressions are just displaying the result for the max selected year and all the months up until the selected month for the max selected year.

Not applicable

## Re: SET expression for compute the Balance sheet.

Hi Sunny

if my raw data is as following :-

31/12/15   20

31/12/15   -55

31/1/16   70

31/1/16   -35

29/2/16   10

29/2/16   -5

So it will display 5. ( Assume now us Feb 29 ) , it will only sum up Feb 2016 raw data which is 10 and -5 right ?

Am i correct ?

Paul

Not applicable

## Re: SET expression for compute the Balance sheet.

Since the expression use <= for Month's set analysis it might be using Jan and Feb. (Although the current structure will only work if Month is in number because max() will output a number and you need to have same formats on the LHS and RHS of  set analysis).

if([Exec P&L Level] = 's','',Sum({\$<year = {\$(=max(year))}, month = {"<=\$(=max({<year={\$(=max(year))}>} month))"}>}[Amount]*-1)/1000/\$(ColumnDim98))

For getting just Feb, may be this:

if([Exec P&L Level] = 's','',Sum({\$<year = {\$(=max(year))}, month = {"\$(=max({<year={\$(=max(year))}>} month))"}>}[Amount]*-1)/1000/\$(ColumnDim98))

Not applicable

## Re: SET expression for compute the Balance sheet.

Hi sunny

Thank you very much for your detail explanation, it help a lot.

Paul

Not applicable

## Re: SET expression for compute the Balance sheet.

Hi sunny

One more related question to SET , but i use the expression P&L instead of balance sheet.

sum({\$<year = {\$(=max(year))}, month = {"<=\$(=max({<year={\$(=max(year))}>} month))"}>} Amount/1000/\$(ColumnDim98))

Can i said the above expression will sum up all amount for the 2016 from jan till feb ? if user select year =2016 month =2.

sum({\$<year = {\$(=max(year))}, Month = {"<=\$(=max({<year={\$(=max(year))}>} Month))>\$(=max({<year={\$(=max(year))}>} Month)-1)"}>}   Amount/1000/\$(ColumnDim98))

The above expression will sum only Feb if user click on year=2016 & month =2

Am i right ?

Paul

Not applicable

## Re: SET expression for compute the Balance sheet.

Yes I believe both the expressions are doing what you mentioned, but why are you doing <=2>1? why not just =2 for your second expression? Both means the same thing

Sum({\$<year = {\$(=Max(year))}, month = {"\$(=Max({<year={\$(=Max(year))}>} month))"}>} Amount/1000/\$(ColumnDim98))

Not applicable

## Re: SET expression for compute the Balance sheet.

Hi sunny

One is compute YTD Jan till Feb , another one is compute Feb amount.

Both expression look the same to me.

Paul

Sent from my iPhone

Not applicable

## Re: SET expression for compute the Balance sheet.

Not sure what you mean, but you can use this for YTD:

Sum({\$<year = {\$(=Max(year))}, month = {"<=\$(=Max({<year={\$(=Max(year))}>} month))"}>} Amount/1000/\$(ColumnDim98))

this for the max month selected in the max year selected

Sum({\$<year = {\$(=Max(year))}, month = {"\$(=Max({<year={\$(=Max(year))}>} month))"}>} Amount/1000/\$(ColumnDim98))

Community Browser