Average Age of Products in Line Chart

[meischl]

Hi Qlik Community,

I have several products with different production start and end dates:

Product	Start of Production	End of Production
Product A	1.1.2018	1.9.2024
Product B	1.1.2020	1.9.2028
Product C	1.6.2021	1.1.2033
Product D	1.2.2023	1.2.2031
Product E	1.8.2025	1.4.2033

Using the start and end dates, I want to create a line chart that shows the average age of my products. It should look something like this:

Products that are not in production at the time of counting should not be added to the average. This means i.e. product A will be excluded from the calculation after September 1st, 2024, as will product E before the start of production.

Does anyone have an idea how I can implement this in a great and easy way in Qlik Sense?

Many thanks for your help!  

[JHuis]

But Age is not the same as production start and production end? (or is it?) 

I think you are missing a value Like:

Product	Start of Production	End of Production	Productlife
Product A	1.1.2018	1.9.2024	1
Product B	1.1.2020	1.9.2028	2
Product C	1.6.2021	1.1.2033	3
Product D	1.2.2023	1.2.2031	4
Product E	1.8.2025	1.4.2033	5

[Vegar]

I have an idea 💡,  but I have not tried this my self.

Connect your products to a master calendar using intervalmatch. This will ensure the criteria that a product will only be associated  with dates between production start and production stop. 

Then make a chart using dates as a dimension and the measure avg(Date-[Start of Production]).

As i wrote, I have not tried this  myself, but it feels like it should work. Please send feedback on the suggestion if you make an attempt using it.

[MarcoWedel · Accepted solution]

one solution could be:

tabProduction:
LOAD Product,
     Date#([Start of Production],'DD.MM.YYYY') as [Start of Production],
     Date#([End of Production],'DD.MM.YYYY') as [End of Production]
Inline [
Product	Start of Production	End of Production
Product A	1.1.2018	1.9.2024
Product B	1.1.2020	1.9.2028
Product C	1.6.2021	1.1.2033
Product D	1.2.2023	1.2.2031
Product E	1.8.2025	1.4.2033
] (delimiter is '\t');

tabDates:
LOAD Product,
     Date([Start of Production]+IterNo()-1) as Date,
     IterNo() as DaysInProduction
Resident tabProduction
While [Start of Production]+IterNo()-1 <= [End of Production];

[meischl]

Thank you Marco, your solution works great!

Best Regards,

Mario

[meischl]

Hi Vegar,

Thanks a lot for your help. In the end I chose Marco's solutions. But your approach helped me a lot elsewhere!

Best Regards,

Mario

[meischl]

I've reached the next level and I have follow-up question 😊 

I have added a grouping by segment in addition to the previous data. The data set now looks like this:

Segment	Product	Start of Production	End of Production
Segment A	Product A	1.1.2018	1.9.2024
Segment A	Product B	1.1.2020	1.9.2028
Segment B	Product C	1.6.2021	1.1.2033
Segment B	Product D	1.2.2023	1.2.2031
Segment B	Product E	1.8.2025	1.4.2033

To filter my dashboard, I created a drill down with the sequence 'Segment - Product'.

Ideally, I would like to see the average age of the entire product range at the beginning. In the next step, the two segments in one chart, then only one segment for itself and then a selected product for itself.

I'm having trouble getting the average age right. I have the following difficulties:

- I don't know how to start the view with an overall average. Thanks to the drill down, my view always starts with showing both segments with their own average age indepedent from each other.

- If I then select a segment, I see all the products at once. Each product shows its own lifetime, but no average is formed.

As a workaround, I tried selecting 'Stacked' for the line chart in the Appearance settings and dividing by the number of segments or products to get the average. Unfortunately I didn't manage to integrate the number with the drill down (Segments: 2, Products depending on the filter for the segment either 2 or 3).

Does anyone have a great idea that could help with my question? 

[meischl]

It looks as if my request was probably a bit too cumbersome, or unfortunately there is no one who can help me. That's why I'm trying to break down my problem a bit. Based on @MarcoWedel 's advice, I calculate DaysInProduction of my products with the following script:

tabDates:

LOAD Product,

     Date([Start of Production]+IterNo()-1) as Date,

     IterNo() as DaysInProduction

Resident tabProduction

While [Start of Production]+IterNo()-1 <= [End of Production];

I would now like to add the number of products that are active each day (Start of Production < Date < End of Production) to the tabDates table in the 'ActiveProducts' field. In addition I would like to do the same for segments in form of a ‘ActiveSegments’ field. I’m hoping that this will help me to calculate the averages.

Thanks a lot in advance!