6 Replies Latest reply: Dec 3, 2014 4:29 AM by neetha P

# Calculate age, display in buckets

Hi everyone,

I have inventory of different products, in a dataset covering several years. I want to calculate the age of each product in each year, dynamically group the products into age buckets based on your selected years, and then display those age buckets in a pie chart. How do I implement this?

So if my dataset currently looks like this:

Year   Product   Delivery Year

2010   A                    1999

2011   A                    1999

2012   A                    1999

2010   B                    2002

2011   B                    2002

2012   B                    2002

.... etc

Year   Product   Delivery Year     Age

2008   A                    1999             9

2010   A                    1999            11

2011   A                    1999            12

2012   A                    1999            13

2010   B                    2008             2

2011   B                    2008             3

2012   B                    2008             4

... etc

I then want to group Products by Age group of 0-5, 5-10 Years for the selected time period. So if I selected 2008, the group count would be:

Group     Distinct Product

0-5                  1

6-10                  1

11-15                0

But if I selected 2012, the group count would be:

Group     Distinct Product

0-5                    1

5-10                  0

11-15                1

If I selected 2008-2012 in the QVW, I would want it to calculate off the latest year, 2012.

Finally, I would then want to display this group count in a pie chart.

Can anyone please tell me step-by-step how to implement? Thanks!

:

• ###### Re: Calculate age, display in buckets

Implementing the Class() function as a dimension of your load script calculated age might work!

Posting a sample .qvw would help.

Class(Age,5) would produce

0-5

6-10

11-15

so on...

• ###### Re: Calculate age, display in buckets

Hi Andrew,

To calculate age and display as buckets,you can use interval function(irregular buckets) or

class function(regular buckets).

if((Age) <= 5, '1-5',

if((Age) <=10 , '6-10',

if((Age) <=15, '11-15',

Other ))) as Bucket,

or

Class(Age,5)

Both have Pros/Cons:

Class Function:

1.Class function creates regular bucket

2.Buckets cannot be named here.

Interval function:

1.Interval function creates irregular bucket

2.Buckets can be named here.

Regards

Neetha

• ###### Re: Calculate age, display in buckets

Hi,

do you have an example for using the Interval() function in this context?

thanks

regards

Marco

• ###### Re: Calculate age, display in buckets

Calculated Dimension:

Class(Year-[Delivery Year],5)

Expression:

Count(Distinct Product)

• ###### Re: Calculate age, display in buckets

Hi Marco,

Please see below example for interval function:

If(Len(Trim([Call Closed - Date/Time])) = 0 ,
If((interval(Date(Today(),'DD-MM-YYYY hh:mm:ss')-Date([Call Opened - Date/Time],'DD-MM-YYYY hh:mm:ss'),'mm'))<=90, 'P1',
If((interval(Date(Today(),'DD-MM-YYYY hh:mm:ss')-Date([Call Opened - Date/Time],'DD-MM-YYYY hh:mm:ss'),'mm'))<=240, 'P2',
If((interval(Date(Today(),'DD-MM-YYYY hh:mm:ss')-Date([Call Opened - Date/Time],'DD-MM-YYYY hh:mm:ss'),'hh'))<=8, 'P3',
If((interval(Date(Today(),'DD-MM-YYYY')-Date([Call Opened - Date/Time],'DD-MM-YYYY'),'DD'))<=1, 'P4',
If((interval(Date(Today(),'DD-MM-YYYY')-Date([Call Opened - Date/Time],'DD-MM-YYYY'),'DD'))<=5, 'P5',
If((interval(Date(Today(),'DD-MM-YYYY')-Date([Call Opened - Date/Time],'DD-MM-YYYY'),'DD'))<=20, 'P6','P7'))))))) as OpenByAge,

Regards

Neetha

• ###### Re: Calculate age, display in buckets

David, Neetha and Marco, thank you!