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.

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

Hello!Nothing works but here's what was in the KB article OS support suggested: Example – need to assign all dimensions in LoopMemberFilterUD2#Root.CustomMemberList(BRName = XFR_MemberListRanked,MemberListName = Ranked, RankType = Top, RankCount = 10, LoopMemberFilter = UD2#Top.Base, DataCellToRankMemberFilter = Cb#|CVCube|:E#[|CVEntity|]:C#|CVConsolidation|:S#|CVScenario|:T#|CVTime|:V#YTD:A#60000:F#[TopFlows]:O#Top:I#Top:U1#Top:U2#Top:U3#Top:U4#Top:U5#None:U6#None:U7#None:U8#None) 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)

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)

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)

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)

Max/Min Calculation Row in CV | OneStream Community

13 Replies

ckattookaran
Valued Contributor
4 years ago
What is your criteria? Show us the member filter from the cube view for the min and max lines.
- NicoleBruno
  Valued Contributor
  4 years ago
  Hello!
  Nothing works but here's what was in the KB article OS support suggested:
  Example – need to assign all dimensions in LoopMemberFilter
  UD2#Root.CustomMemberList(BRName = XFR_MemberListRanked,MemberListName = Ranked, RankType = Top, RankCount = 10, LoopMemberFilter = UD2#Top.Base, DataCellToRankMemberFilter = Cb#|CVCube|:E#[|CVEntity|]:C#|CVConsolidation|:S#|CVScenario|:T#|CVTime|:V#YTD:A#60000:F#[TopFlows]:O#Top:I#Top:U1#Top:U2#Top:U3#Top:U4#Top:U5#None:U6#None:U7#None:U8#None)
  
  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
    Valued Contributor
    4 years ago
    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)

Forum Discussion

Max/Min Calculation Row in CV

13 Replies

Related Content

Member Formula - Min/Max Calculations

Cube-view: Calculate a column based on a calculated row

Calculate variance percentages for different rows

About Finance Rules and Calculations

Max Calculation in CV