Creating a Variable to Store a ValueList

[BZane]

Hi everyone,

We currently have 27 fields of data that represent different aging buckets for our AR.  In order to show that data trending over time, I have previously created a master dimension with a value list of all of the field names, and then a master measure with a bunch of nested if statements calling for the sum of that aging bucket if that is the appropriate field and naming it as such.  However, I have now reached the maximum character limit in the expression due to the number of aging buckets and nested if statements.  I was trying to use a variable instead and have tried a variety of approaches, with no luck.  How would I store the valuelist function into a variable so as to replace the below expression:

if(ValueList('0-30','0-45','0-60','0-90','31-60','31-90','46-75','61-90','76-90','91-120','91-150','91-180','121+','121-150','151-180','151-210','181+','181-240','181-270','181-364','181-365','211+','211-365','241+','271-365','365+','366+')='0-30',sum({$<[Metric]={'A/R'},[KPI Date.autoCalendar.MonthsAgo]={"<=6"}>}[0-30])) 

*this is just one of many nested if statements*

I tried the following values in the variable (agefields):

=ValueList('0-30','0-45','0-60','0-90','31-60','31-90','46-75','61-90','76-90','91-120','91-150','91-180','121+','121-150','151-180','151-210','181+','181-240','181-270','181-364','181-365','211+','211-365','241+','271-365','365+','366+')

ValueList('0-30','0-45','0-60','0-90','31-60','31-90','46-75','61-90','76-90','91-120','91-150','91-180','121+','121-150','151-180','151-210','181+','181-240','181-270','181-364','181-365','211+','211-365','241+','271-365','365+','366+')

chr(39) & '0-30' & chr(39) & ',' & chr(39) & '0-45' & chr(39) & ',' & chr(39) & '0-60' & chr(39) & ',' & chr(39) & '0-90' & chr(39) & ',' & chr(39) & '31-60' & chr(39) & ',' & chr(39) & '31-90' & chr(39) & ',' & chr(39) & '46-75' & chr(39) & ',' & chr(39) & '61-90' & chr(39) & ',' & chr(39) & '76-90' & chr(39) & ',' & chr(39) & '91-120' & chr(39) & ',' & chr(39) & '91-150' & chr(39) & ',' & chr(39) & '91-180' & chr(39) & ',' & chr(39) & '121+' & chr(39) & ',' & chr(39) & '121-150' & chr(39) & ',' & chr(39) & '151-180' & chr(39) & ',' & chr(39) & '151-210' & chr(39) & ',' & chr(39) & '181+' & chr(39) & ',' & chr(39) & '181-240' & chr(39) & ',' & chr(39) & '181-270' & chr(39) & ',' & chr(39) & '181-364' & chr(39) & ',' & chr(39) & '181-365' & chr(39) & ',' & chr(39) & '211+' & chr(39) & ',' & chr(39) & '211-365' & chr(39) & ',' & chr(39) & '241+' & chr(39) & ',' & chr(39) & '271-365' & chr(39) & ',' & chr(39) & '365+' & chr(39) & ',' & chr(39) & '366+' & chr(39) 

^in this case i kept the "ValueList" in the expression string^

None of these have worked.  Any help would be greatly appreciated. Thanks!

[Seyko · Accepted solution]

Hello,

You can create a variable in the load editor with a SET statement like this:

SET vList = '0-30','0-45','0-60','0-90','31-60','31-90','46-75','61-90','76-90','91-120','91-150','91-180','121+','121-150',
'151-180','151-210','181+','181-240','181-270','181-364','181-365','211+','211-365','241+','271-365','365+','366+';

After you define this variable like a paramater in your valuelist function:

ValueList($(vList))

 I hope which will help you.

cordially.

[BZane]

This worked like a charm!  Thanks!  I guess all I was missing was adding the $ before the variable in the ValueList function.  I'm assuming it would have worked by creating the variable in the app itself as well, but I was able to create it in the load script and it worked great.  Thanks!