topic Re: SET expression for compute the Balance sheet. in QlikView

SET expression for compute the Balance sheet.

paulyeo11 — Mon, 29 Feb 2016 09:13:35 GMT

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

Re: SET expression for compute the Balance sheet.

jagan — Mon, 29 Feb 2016 09:19:35 GMT

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.

Re: SET expression for compute the Balance sheet.

sunny_talwar — Mon, 29 Feb 2016 09:22:00 GMT

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.

Re: SET expression for compute the Balance sheet.

paulyeo11 — Mon, 29 Feb 2016 09:43:45 GMT

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

Re: SET expression for compute the Balance sheet.

sunny_talwar — Mon, 29 Feb 2016 09:47:47 GMT

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

Re: SET expression for compute the Balance sheet.

paulyeo11 — Mon, 29 Feb 2016 09:54:34 GMT

Hi sunny

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

Paul

Re: SET expression for compute the Balance sheet.

paulyeo11 — Mon, 29 Feb 2016 10:04:20 GMT

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

Re: SET expression for compute the Balance sheet.

sunny_talwar — Mon, 29 Feb 2016 10:07:41 GMT

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

Re: SET expression for compute the Balance sheet.

paulyeo11 — Mon, 29 Feb 2016 11:56:53 GMT

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

Re: SET expression for compute the Balance sheet.

sunny_talwar — Mon, 29 Feb 2016 12:00:40 GMT

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