OneStream Community

NicoleBruno · ‎04-01-2022

Hello!

I received a request to add a maximum and minimum line to a cube view. The cube view shows entities in columns and a variety of accounts in the rows (not together in a hierarchy, so no .base or .children, just a list separated by commas).

Max/min aren't available in the "getdatacell expressions" area so I emailed support to see if it's possible and they directed me to a ranking BR in GS. I loaded the BR, added a row to my CV and updated the pull but still am getting no data. Honestly, I don't think this BR and pull are the solution for what I'm trying to do but I don't know of any other way.

Do any of you have samples of pulling something similar? Were you able to get a max and min in a CV via CV math or some other way?

Appreciate any help I can get - thanks!

Here's the KB article provided for reference:

And the GS CV is in CVs > XFR Member filters (E) Member list using business rule > XFR Member List (C) Ranked Entity Sales.

ckattookaran · ‎04-01-2022

What is your criteria? Show us the member filter from the cube view for the min and max lines.

NicoleBruno · ‎04-04-2022

Hello!

Nothing works but here's what was in the KB article OS support suggested:

Example – need to assign all dimensions in LoopMemberFilter

And this is the sample pull suggested in the Golfstream BR:

E#Root.CustomMemberList(BRName = XFR_MemberListRanked, MemberListName = Ranked, RankType = (Top,Bottom,Min,Max), RankCount = X, LoopMemberFilter = E#Root.Base, DataCellToRankMemberFilter = CB#:E#... )

This is what I tried and got nothing (no rows added to the CV):

A#Root.CustomMemberList(BRName = XFR_MemberListRanked, MemberListName = Ranked, RankType = (Min), RankCount = 1, LoopMemberFilter = A#Assets_Tot, DataCellToRankMemberFilter = Cb#Global:C#local:S#Actual:V#YTD:F#EndBal:O#Top:I#Top:U1#ISNaturalDetail_TOT:U2#Total_Product:U4#TopUD4_NDC:U5#TopUD5:U6#TopUD6_DPT:U7#None:U8#None)

And this one gave me an error:

A#Root.CustomMemberList(BRName = XFR_MemberListRanked, MemberListName = Ranked, RankType = (Min), RankCount = 1, LoopMemberFilter = A#Assets_Tot, DataCellToRankMemberFilter = Cb#Global:C#local:S#Actual:V#YTD:F#EndBal:O#Top:I#Top:U1#ISNaturalDetail_TOT:U2#Total_Product:U4#TopUD4_NDC:U5#TopUD5:U6#TopUD6_DPT:U7#None:U8#None)

ckattookaran · ‎04-04-2022

Try this one.

A#Root.CustomMemberList(BRName = XFR_MemberListRanked, MemberListName = Ranked, RankType =Min, RankCount = 1, LoopMemberFilter = A#Assets_Tot, DataCellToRankMemberFilter = Cb#Global:C#local:S#Actual:V#YTD:F#EndBal:O#Top:I#Top:U1#ISNaturalDetail_TOT:U2#Total_Product:U4#TopUD4_NDC:U5#TopUD5:U6#TopUD6_DPT:U7#None:U8#None)

NicoleBruno · ‎04-04-2022

Hello!

I noticed those parenthesis as I was copying the formulas, thanks. The line does show up on the CV now but it's not what I actually want anyway. I don't know how to add the account filter to the formula because right now, it's just showing the Assets_Tot line because that's what I'm telling it to show. It's not actually showing the min/max of anything, just one line.

The account filter which I want the max/min of is a bunch of accounts in a list but when I try to add them to the formula, it only picks up whichever account is listed first. Here's what it looks like:

A#Root.CustomMemberList(BRName = XFR_MemberListRanked, MemberListName = Ranked, RankType = Min, RankCount = 1, LoopMemberFilter =
A#Assets_TOT, A#CurrentAssets_CA, A#CashPlusEquiv_CA, A#RestCashPlusEquiv_CA, A#MarkSec_CA, A#AllARNet_CA, A#InventoryNet_CA, A#Prepaid_CA,
A#IncTaxReceive_CA, A#IncTaxDeferreds_CA, A#ICRecAll_CA, A#MiscAssets_CA, A#HeldForSale_CA, A#NoncurrentAssets_NA, A#MarkSec_NA, A#PPENet_NA, A#OpLeaseNet_NA, A#GoodwillNet_NA, A#IntangNet_NA, A#IncTaxReceive_NA, A#IncTaxDeferreds_NA, A#ICRecAll_NA, A#ICInvest_NA, A#RestCashPlusEquiv_NA, A#AllARNet_NA, A#InventoryNet_NA, A#MiscAssets_NA, A#LiabsPlusEquity_TOT, A#Liabs_TOT, A#CurrentLiabs_CL, A#AP_Accr_CL,
A#330_500000, A#Debt_CL, A#AccrSettlements_CL, A#IncTaxPay_CL, A#IncTaxDeferreds_CL, A#ICPayAll_CL, A#HeldForSale_CL, A#NoncurrentLiabs_NL,
A#425_250000, A#Debt_NL, A#IncTaxPay_NL, A#IncTaxDeferreds_NL, A#ICPayAll_NL, A#MiscLiabs_NL, A#Accr_NL, A#AccrRestruct_NL, A#AccrSettlements_NL, A#AccrGeneral_NL, A#OthMiscLiabs_NL, A#Equity_TOT, A#EquityWithNCI_SE, A#CapitalEquity_SE, A#PrefShares_SE, A#OrdShares_SE, A#510_300000, A#APICAll_SE, A#510_500000, A#REAll_SE, A#AOCI_SE, A#EquityOther_SE, A#530_100000, A#NetIncNoNCI_TOT, A#NetIncWithNCI_TOT, A#NetIncCont_TOT, A#PreTaxInc_TOT, A#OpInc_TOT, A#GM_TOT, A#RevenuesNet_TOT, A#CoGS_TOT, A#OpExp_TOT, A#OpExpNatural_TOT, A#Impair_TOT, A#110, A#OpExpFunctional_TOT, A#AcqInt_TOT, A#SGA_TOT, A#RD_TOT, A#NonOpItems_TOT, A#InterestAll_TOT, A#OtherItems_TOT, A#780, A#OtherItemsGeneral_TOT, A#790, A#FX_TOT, A#GLSaleAsset_TOT, A#GLInvest_TOT, A#IncTax_TOT, A#810, DataCellToRankMemberFilter = Cb#Global:C#local:S#Actual:V#YTD:F#EndBal:O#Top:I#Top:U1#ISNaturalDetail_TOT:U2#Total_Product:U4#TopUD4_NDC:U5#TopUD5:U6#TopUD6_DPT:U7#None:U8#None)

ckattookaran · ‎04-04-2022

It could be because the account members are listed as comma separated.

Try this.

A#Root.CustomMemberList(BRName = XFR_MemberListRanked, MemberListName = Ranked, RankType = Min, RankCount = 1, LoopMemberFilter =
A#Root.List([Assets_TOT], [CurrentAssets_CA], [CashPlusEquiv_CA], [RestCashPlusEquiv_CA], [MarkSec_CA], [AllARNet_CA], [InventoryNet_CA], [Prepaid_CA], [IncTaxReceive_CA], [IncTaxDeferreds_CA], [ICRecAll_CA], [MiscAssets_CA], [HeldForSale_CA], [NoncurrentAssets_NA], [MarkSec_NA], [PPENet_NA], [OpLeaseNet_NA], [GoodwillNet_NA], [IntangNet_NA], [IncTaxReceive_NA], [IncTaxDeferreds_NA], [ICRecAll_NA], [ICInvest_NA], [RestCashPlusEquiv_NA], [AllARNet_NA], [InventoryNet_NA], [MiscAssets_NA], [LiabsPlusEquity_TOT], [Liabs_TOT], [CurrentLiabs_CL], [AP_Accr_CL], [330_500000], [Debt_CL], [AccrSettlements_CL], [IncTaxPay_CL], [IncTaxDeferreds_CL], [ICPayAll_CL], [HeldForSale_CL], [NoncurrentLiabs_NL], [425_250000], [Debt_NL], [IncTaxPay_NL], [IncTaxDeferreds_NL], [ICPayAll_NL], [MiscLiabs_NL], [Accr_NL], [AccrRestruct_NL], [AccrSettlements_NL], [AccrGeneral_NL], [OthMiscLiabs_NL], [Equity_TOT], [EquityWithNCI_SE], [CapitalEquity_SE], [PrefShares_SE], [OrdShares_SE], [510_300000], [APICAll_SE], [510_500000], [REAll_SE], [AOCI_SE], [EquityOther_SE], [530_100000], [NetIncNoNCI_TOT], [NetIncWithNCI_TOT], [NetIncCont_TOT], [PreTaxInc_TOT], [OpInc_TOT], [GM_TOT], [RevenuesNet_TOT], [CoGS_TOT], [OpExp_TOT], [OpExpNatural_TOT], [Impair_TOT], [110], [OpExpFunctional_TOT], [AcqInt_TOT], [SGA_TOT], [RD_TOT], [NonOpItems_TOT], [InterestAll_TOT], [OtherItems_TOT], [780], [OtherItemsGeneral_TOT], [790], [FX_TOT], [GLSaleAsset_TOT], [GLInvest_TOT], [IncTax_TOT], [810]), DataCellToRankMemberFilter = Cb#Global:C#local:S#Actual:V#YTD:F#EndBal:O#Top:I#Top:U1#ISNaturalDetail_TOT:U2#Total_Product:U4#TopUD4_NDC:U5#TopUD5:U6#TopUD6_DPT:U7#None:U8#None)

ckattookaran · ‎04-04-2022

Oh, one more thing in your DataCellToRankMemberFilter there is no Account, you need to mention an account in there.

NicoleBruno · ‎04-06-2022

Hello!

Thanks so much! I tried the formula you listed above and popped in a random account and I'm getting this error:

I'm not sure I understand what "DataCellToRankMemberFilter" actually means so I just put one of the accounts from my filter in there.

Thanks for the help so far!

ckattookaran · ‎04-06-2022

Click the ellipses and scroll to the bottom; copy and paste the error here. We need to check which function/subroutine is throwing the error.

NicoleBruno · ‎04-07-2022

Hello!

I'm pretty sure the DataCellToRankMemberFilter is incorrect because I'm not actually sure what it means so I just guessed at what to include. I've asked OS support for a definition so hopefully that helps direct me. Here's how the formula currently looks:

A#Root.CustomMemberList(BRName = XFR_MemberListRanked, MemberListName = Ranked, RankType = Min, RankCount = 1, LoopMemberFilter =
A#Root.List([Assets_TOT], [CurrentAssets_CA], [CashPlusEquiv_CA], [RestCashPlusEquiv_CA], [MarkSec_CA], [AllARNet_CA], [InventoryNet_CA], [Prepaid_CA], [IncTaxReceive_CA], [IncTaxDeferreds_CA], [ICRecAll_CA], [MiscAssets_CA], [HeldForSale_CA], [NoncurrentAssets_NA], [MarkSec_NA], [PPENet_NA], [OpLeaseNet_NA], [GoodwillNet_NA], [IntangNet_NA], [IncTaxReceive_NA], [IncTaxDeferreds_NA], [ICRecAll_NA], [ICInvest_NA], [RestCashPlusEquiv_NA], [AllARNet_NA], [InventoryNet_NA], [MiscAssets_NA], [LiabsPlusEquity_TOT], [Liabs_TOT], [CurrentLiabs_CL], [AP_Accr_CL], [330_500000], [Debt_CL], [AccrSettlements_CL], [IncTaxPay_CL], [IncTaxDeferreds_CL], [ICPayAll_CL], [HeldForSale_CL], [NoncurrentLiabs_NL], [425_250000], [Debt_NL], [IncTaxPay_NL], [IncTaxDeferreds_NL], [ICPayAll_NL], [MiscLiabs_NL], [Accr_NL], [AccrRestruct_NL], [AccrSettlements_NL], [AccrGeneral_NL], [OthMiscLiabs_NL], [Equity_TOT], [EquityWithNCI_SE], [CapitalEquity_SE], [PrefShares_SE], [OrdShares_SE], [510_300000], [APICAll_SE], [510_500000], [REAll_SE], [AOCI_SE], [EquityOther_SE], [530_100000], [NetIncNoNCI_TOT], [NetIncWithNCI_TOT], [NetIncCont_TOT], [PreTaxInc_TOT], [OpInc_TOT], [GM_TOT], [RevenuesNet_TOT], [CoGS_TOT], [OpExp_TOT], [OpExpNatural_TOT], [Impair_TOT], [110], [OpExpFunctional_TOT], [AcqInt_TOT], [SGA_TOT], [RD_TOT], [NonOpItems_TOT], [InterestAll_TOT], [OtherItems_TOT], [780], [OtherItemsGeneral_TOT], [790], [FX_TOT], [GLSaleAsset_TOT], [GLInvest_TOT], [IncTax_TOT], [810]), DataCellToRankMemberFilter = Cb#|CVCube|:E#[|CVEntity|]:C#|CVConsolidation|:S#|CVScenario|:T#|CVTime|:V#YTD:A#780:F#EndBal:O#Top:I#Top:U1#ISNaturalDetail_TOT:U2#Total_Product:U3#|CVUD3|:U4#TopUD4_NDC:U5#TopUD5:U6#TopUD6_DPT:U7#None:U8#None)

ckattookaran · ‎04-07-2022

That means you provide the intersection of data to do the ranking. All the members except the one you are looping must point to the correct intersection. The code gets the data using the loop (in your case, it gets data for the accounts), then performs the ranking.

ckattookaran · ‎04-07-2022

Please post the full message, with that error you need to at least point to the function that is causing the call. That's all we can check for now.

NicoleBruno · ‎04-07-2022

I don't understand. If the DataCellToRankMemberFilter is defining the rest of the dimensions which are then using the LoopMemberToFilter, why would I include one (or any) account in the DataCellToRankMemberFilter?

The wording of the error is pasted in an earlier message but here's a screenshot also:

Thank you!

ckattookaran · ‎04-08-2022

You are right, it is using a MemberScriptBuilder, so even if you don't supply it. I just wanted it to have everything so that we can narrow it down. However, this seems like you need to enable logging inside the GetRankedMembers function to see which line is failing.

OneStream Community

Max/Min Calculation Row in CV