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:
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:
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:
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!
