Announcements
cancel
Showing results for
Did you mean:
Contributor II

## Average Age of Products in Line Chart

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?

Labels (2)

• ### Set Analysis

1 Solution

Accepted Solutions
MVP

one solution could be:

``````tabProduction:
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:
Date([Start of Production]+IterNo()-1) as Date,
IterNo() as DaysInProduction
Resident tabProduction
While [Start of Production]+IterNo()-1 <= [End of Production];``````

7 Replies
Creator III

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
MVP

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.

MVP

one solution could be:

``````tabProduction:
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:
Date([Start of Production]+IterNo()-1) as Date,
IterNo() as DaysInProduction
Resident tabProduction
While [Start of Production]+IterNo()-1 <= [End of Production];``````

Contributor II
Author

Thank you Marco, your solution works great!

Best Regards,

Mario

Contributor II
Author

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

Contributor II
Author

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?

Contributor II
Author

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:

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.