Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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))
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.
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.
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
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))
Hi sunny
Thank you very much for your detail explanation, it help a lot.
Paul
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
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))
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 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))