List dimension based on expression values

[#1]

Hi,

I'm running into a bit of a snag, because I'm trying to create some dimension values based on expression values.  I have a straight table where I've added an expression (labeled as Bucket) which contains the following nested formula:

if(Age<=0,'Current',if(Age>=1 and Age<16,'1 - 15',if(Age>=16 and Age<31,'16 - 30',if(Age>=31 and Age<61,'31 - 60',if(Age>=61 and Age<91,'61 - 90',if(Age>=91 and Age<121,'91 - 120',if(Age>=121 and Age<151,'121 - 150',if(Age>=151 and Age<181,'151 - 180','180 +'))))))))

You're probably wondering why I have a nested formula in my straight table.  It's because Age is an expression field within the straight table that is calculated after plugging in a date into an input box.  I'm unable to include this date field in the load script due to the date changing based on the data source as of date.

So...back to my question.

How can I create a list table which has a dimension that produces the following values to help me filter my straight table.  I'd also like to include an expression field in the list table to calculate the sums of the dimension values.

Current

1 - 15

16 - 30

31 - 60

61 - 90

91 - 120

121 - 150

151 - 180

180 +

[sunny_talwar]

You can use the above expression (but with original expression behind Age) and surround it with Aggr() function...

Aggr(

if(Age<=0,'Current',

if(Age>=1 and Age<16,'1 - 15',

if(Age>=16 and Age<31,'16 - 30',

if(Age>=31 and Age<61,'31 - 60',

if(Age>=61 andAge<91,'61 - 90',

if(Age>=91 and Age<121,'91 - 120',

if(Age>=121 and Age<151,'121 - 150',

if(Age>=151 and Age<181,'151 - 180','180 +')))))))), <Dimensions>)

NOTE:

1) Make sure to replace Age with the actual expression

2) Add all the dimension from your straight table which you have used to create this bucket.

[#3]

Like this?  Plug this calculation in as a calculated dimension?

=Aggr(

if((Aging-[Due Date])<=0,'Current',

if((Aging-[Due Date])>=1 and (Aging-[Due Date])<16,'1 - 15',

if((Aging-[Due Date])>=16 and (Aging-[Due Date])<31,'16 - 30',

if((Aging-[Due Date])>=31 and (Aging-[Due Date])<61,'31 - 60',

if((Aging-[Due Date])>=61 and (Aging-[Due Date])<91,'61 - 90',

if((Aging-[Due Date])>=91 and (Aging-[Due Date])<121,'91 - 120',

if((Aging-[Due Date])>=121 and (Aging-[Due Date])<151,'121 - 150',

if((Aging-[Due Date])>=151 and (Aging-[Due Date])<181,'151 - 180','180 +')))))))),<Dimensions>)

I tried it, but it doesn't work.

If I use [Due Date] as a dimension, my list becomes way too long.  I only want the dimension values to be the bucket info.

[sunny_talwar]

What is your dimension which you used to create the buckets in the straight table?

[#5]

The dimension that I used to create the buckets in the straight table are as follows:

Age (Aging-[Due Date] )

Due Date (included in loan script)

[#6]

[effinty2112]

Hi Vic,

               You could consider using IntervalMatch to create age groups.

[Age Groups]:

LOAD * INLINE [

    Age Group, From, To

    1 - 15, 1, 15

    16 - 30, 16, 30

    31 - 60, 31, 60

    61 - 90, 61, 90

    91 - 120, 91, 120

    121 - 150, 121, 150

    151 - 180, 151, 179

    180 +, 180, 9999

];

Data: //some dummy data

LOAD

Ceil(Rand()*300) as Age,

Round(Rand()*1000) as Measure

AutoGenerate 10000;

IntervalMatch:

IntervalMatch(Age) LOAD From, To Resident [Age Groups];

Result:

Age Group	Sum(Measure)
	4983248
1 - 15	253882
16 - 30	256209
31 - 60	502305
61 - 90	470784
91 - 120	511971
121 - 150	503987
151 - 180	495795
180 +	1988315

Hope this helps

Cheers

Andrew

[#8]

I would do this, however the age is not determined until after the load has been completed.  The age field is determined by a date that I enter in after the load script is done.  If there were a way that I could include in the load script to create a field "As of date" which was determined by the 2nd half of the source date file name, then this I think would be the best solution.

Is there any way to do this?  Create a date field during the load script which is determined by the source file name?

[marcus_sommer]

If you used equally buckets you could try it with an expression like:

pick(ceil((Aging-[Due Date]) / 30) + 1, 'Current', '1 - 30', '31 - 60', …)

Even if this isn't suitable for you it could be better (especially regarding to possible performance issues) to use a pick(match()) combination instead of nested if-loops and/or aggr-functions.

- Marcus

[effinty2112]

Hi Vic,

               If your input date is held in a variable vInput then try this calculated dimension:

=Pick(Rangemin(13,Ceil(([Due Date]-'$(vInput)')/15)),

'1-15',

'16-30',

'31-60',

'31-60',

'61-90',

'61-90',

'91-120',

'91-120',

'121-150',

'121-150',

'151-180',

'151-180',

'180+')

Sort it by Min([Due Date]) descending.