Nested ValueLists with Conditional Logic

[alandilworth]

Hello, I am wanting to know if there is anyway to have nested ValueLists as a dimension using conditional logic.  In the image below I am wanting to only have ‘Commissions’ and ‘Cash’ under ‘Net Commissions’ and ‘Phranchise Fees’ and ‘Technology Fees’ under ‘Net Fees’.  I currently have these three ValueLists as row dimensions in the following order:

=ValueList('Net Cash Payout')
=ValueList('Net Commissions', 'Net Fees')
=ValueList('Commissions', 'Cash', 'Phranchise Fee', 'Technology Fees')

I have tried the following expression for my third dimension but it returns null values:

=IF(ValueList('Net Commissions', 'Net Fees') = 'Net Commissions',
ValueList('Commissions', 'Cash'),
IF(ValueList('Net Commissions', 'Net Fees') = 'Net Fees',
ValueList('Phranchise Fee', 'Technology Fees')))


Thanks!

[Sammy_AK]

Use Pick and match function, instead of nested IFs.

Pick(Match(ValueList('Net Commissions', 'Net Fees'),'Net Commissions')+1

,ValueList('Phranchise Fee', 'Technology Fees')

,ValueList('Commissions', 'Cash')

)

[jwjackso]

Not what you are asking, but our account tree is 19 nodes deep.  I used the Hierarchy  and HiearchyBelongsTo functions to create the visualization that you want.  The dimension was the Node1, Node2, etc...

The expression was Sum({<Ancestor={'Net Commissions','Net Fees'}>}Amount) to get only the accounts that I needed to display.

[alandilworth]

I'm still getting null values with this.  Thank you for the suggestion though

[Sammy_AK]

what is the measure you are using? because we use Value list to populate dimensions as these are stand alone dimensions and do not have relation with data / measure, you need to write measure with similar pick and match statement to match the dimensions. 

[alandilworth]

It is the dimension that is becoming null I believe.  Here is a screenshot of what happens when I try your solution (the same thing happens with what I've tried above).

For the table screenshot in my initial question I am using this as a Dimension (3 dimensions):
=ValueList('Net Cash Payout')
=ValueList('Net Commissions', 'Net Fees')
=ValueList('Commissions', 'Cash', 'Phranchise Fee', 'Technology Fees')

And this as a measure:
=IF(ValueList('Commissions', 'Cash', 'Phranchise Fee', 'Technology Fees') = 'Commissions', if(Sum(CalculatedWAC) > 0, SUM(CalculatedWAC)),
IF(ValueList('Commissions', 'Cash', 'Phranchise Fee', 'Technology Fees') = 'Cash', if(Sum(CalculatedCash) > 0, SUM(CalculatedCash)),
IF(ValueList('Commissions', 'Cash', 'Phranchise Fee', 'Technology Fees') = 'Phranchise Fee', phranFee,
IF(ValueList('Commissions', 'Cash', 'Phranchise Fee', 'Technology Fees') = 'Technology Fees', if(sum(TechFeeAmount) > 0, sum(TechFeeAmount))))))

I have tried this which gives the null results in my provided screenshot. (3 dimensions):
=ValueList('Net Cash Payout')
=ValueList('Net Commissions', 'Net Fees')
=IF(ValueList('Net Commissions', 'Net Fees') = 'Net Commissions',
ValueList('Commissions', 'Cash'),
IF(ValueList('Net Commissions', 'Net Fees') = 'Net Fees',
ValueList('Phranchise Fee', 'Technology Fees')))

And this as the measure:
=IF(ValueList('Commissions', 'Cash') = 'Commissions', if(Sum(CalculatedWAC) > 0, SUM(CalculatedWAC)),
IF(ValueList('Commissions', 'Cash',) = 'Cash', if(Sum(CalculatedCash) > 0, SUM(CalculatedCash)),
IF(ValueList('Phranchise Fee', 'Technology Fees') = 'Phranchise Fee', phranFee,
IF(ValueList('Phranchise Fee', 'Technology Fees') = 'Technology Fees', if(sum(TechFeeAmount) > 0, sum(TechFeeAmount))))))

[Sammy_AK]

i see that when you input the child values, you want to roll up to parent values ? is this the expectation or values do not display?

from the above i understand you have 3 dimensions 

• Dimension 1 =ValueList('Net Cash Payout') 
• Dimension 2 =ValueList('Net Commissions', 'Net Fees')
• Dimension 3 =ValueList('Commissions', 'Cash', 'Phranchise Fee', 'Technology Fees')

in the measure i see that you have provided values for Dimension 3 only.  to do a test of this expression, i replaced sum() with numbers to see if they are being displayed, so i used the below expression for measure.

=IF(ValueList('Commissions', 'Cash', 'Phranchise Fee', 'Technology Fees') = 'Commissions' , 11
, IF(ValueList('Commissions', 'Cash', 'Phranchise Fee', 'Technology Fees') = 'Cash' , 12
, IF(ValueList('Commissions', 'Cash', 'Phranchise Fee', 'Technology Fees') = 'Phranchise Fee', 13
,IF(ValueList('Commissions', 'Cash', 'Phranchise Fee', 'Technology Fees') = 'Technology Fees' , 14))))

i was able to get an output for all the dimension 3 , from this i understand that you are inputting sum() expression which is trying to aggregate values using the dimension 3 and it does not exist because its a valuelist. so instead of using sum(), use Sum (total (<field name>) this will give you the output.  

if you want a roll-up values to be displayed, you need use dimensionality function in your expression.