topic Re: Product Portfolio Age Analysis in QlikView

Product Portfolio Age Analysis

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Can someone assist with the attached QV application. I am calculating product portfolio age based on an "Action Date" and today' s date. I have two ages, one is the PortfolioAge and the other PortfolioAgeLong. The PortfolioAge is in numeric form so that i can be able to use the class function to build charts with product age class intervals. The PortfolioAgeLong should give the true product age from the "ActionDate" in years, months and days.

The application seems not to be calculating the right ages as can be seen from the example below when reference is made to the ActionDate and today's date which means there is something wrong with the script:

Please assist

Regards.

Chris

Message was edited by: Christopher Chitemerere Removed the calculation of fractional ages as this has been answered by Gysbert

Re: Product Portfolio Age Analysis

Gysbert_Wassenaar — Fri, 18 Mar 2016 08:10:23 GMT

The Age function returns an integer value. If you want fractions the you'll have to use something like (Today()-MCAZActionDate)/365

Re: Product Portfolio Age Analysis

Anonymous — Fri, 18 Mar 2016 08:17:20 GMT

Many thanks Gysbert for an answer to the last part of my question. The ages calculated in the application are wrong, can you assist on this through the attached application?

Re: Product Portfolio Age Analysis

Gysbert_Wassenaar — Fri, 18 Mar 2016 08:19:22 GMT

See here:Re: Age in Year Month Day Format‌

age(Today(),MCAZActionDate) & ' Years and ' &(if(day(Today()) >= day(MCAZActionDate),

mod((Year(Today())*12+Month(Today()))-(Year(MCAZActionDate)*12+Month(MCAZActionDate)),12),

if(mod((Year(Today())*12+Month(Today()))-(Year(MCAZActionDate)*12+Month(MCAZActionDate)),12)=0,11,mod((Year(Today())*12+Month(Today()))-(Year(MCAZActionDate)*12+Month(MCAZActionDate)),12)-1))) &' Months and ' &

(if(day(Today()) >= day(MCAZActionDate), day(Today()) - day(MCAZActionDate), Today()-AddMonths(MCAZActionDate,12*age(Today(),MCAZActionDate)+mod(Year(Today())*12+Month(Today())-Year(MCAZActionDate)*12+Month(MCAZActionDate),12)-1))+1) & ' days'

Re: Product Portfolio Age Analysis

Anonymous — Fri, 18 Mar 2016 11:22:34 GMT

Thank you very much

Regards.