topic Re: Nested ValueLists with Conditional Logic in App Development

Nested ValueLists with Conditional Logic

alandilworth — Wed, 26 May 2021 14:26:17 GMT

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!

Re: Nested ValueLists with Conditional Logic

Sammy_AK — Wed, 26 May 2021 19:18:53 GMT

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

)

Re: Nested ValueLists with Conditional Logic

jwjackso — Wed, 26 May 2021 19:19:10 GMT

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.

Re: Nested ValueLists with Conditional Logic

alandilworth — Thu, 27 May 2021 14:13:34 GMT

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

Re: Nested ValueLists with Conditional Logic

Sammy_AK — Fri, 28 May 2021 06:12:21 GMT

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.

Re: Nested ValueLists with Conditional Logic

alandilworth — Fri, 28 May 2021 21:09:25 GMT

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

Re: Nested ValueLists with Conditional Logic

Sammy_AK — Mon, 31 May 2021 08:27:21 GMT

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.