Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

paulyeo11
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
sunny_talwar
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
jagan
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.

sunny_talwar
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.

paulyeo11
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

sunny_talwar
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))

paulyeo11
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

paulyeo11
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

sunny_talwar
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))

paulyeo11
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

sunny_talwar
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))