topic Calculate age, display in buckets in QlikView

Calculate age, display in buckets

Tue, 02 Dec 2014 19:38:53 GMT

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

I want to add Age

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

Tue, 02 Dec 2014 20:43:12 GMT

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

Anonymous — Tue, 02 Dec 2014 21:12:47 GMT

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,

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

MarcoWedel — Tue, 02 Dec 2014 21:14:42 GMT

Calculated Dimension:

Class(Year-[Delivery Year],5)

Expression:

Count(Distinct Product)

Re: Calculate age, display in buckets

MarcoWedel — Tue, 02 Dec 2014 21:16:42 GMT

Hi,

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

thanks

regards

Marco

Re: Calculate age, display in buckets

Tue, 02 Dec 2014 22:08:38 GMT

David, Neetha and Marco, thank you!

Re: Calculate age, display in buckets

Anonymous — Wed, 03 Dec 2014 09:29:05 GMT

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