SET expression for compute the Balance sheet.

[paulyeo11]

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

[jagan]

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]

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]

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 · Accepted solution]

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]

Hi sunny

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

Paul

[paulyeo11]

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]

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]

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