Creating a Variable to Store a ValueList

BZane — Sat, 16 Nov 2024 06:14:46 GMT

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!

Re: Creating a Variable to Store a ValueList

Seyko — Tue, 26 Mar 2019 23:01:39 GMT

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.

Re: Creating a Variable to Store a ValueList

BZane — Wed, 27 Mar 2019 02:27:54 GMT

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!

topic Re: Creating a Variable to Store a ValueList in App Development

Creating a Variable to Store a ValueList

Re: Creating a Variable to Store a ValueList

Re: Creating a Variable to Store a ValueList