topic Re: Calculating product portfolio age in QlikView

Calculating product portfolio age

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

I want to calculate the age of a product portfolio in years, months and days based on a field 'stat_date' and today's date and i am trying the following:

[MCAZAprroved]:
LOAD APPLICANT as MCAZApplicant,
If(Match(Trim(CATEFORY), 'HRA', 'HR', 'PID', 'PIM', 'P', 'PR', 'VMGD'), 'OTC',If(Match(Trim(CATEFORY), 'PP', 'PP10', 'N'), 'PRESCRIPTION', CATEFORY)) AS MCAZCategory,
FORM as [MCAZ Dosage Form & Route of Admin],
"GENERIC NAME" as [MCAZ Active Ingredient] ,
MANUFACTURER as Manufacturer,
"REGISTRATION NUMBER" as [RegistrationNumber],
Date("stat_date") as MCAZActionDate,
STRENGTH as [MCAZ Strength],
"TRADE NAME" as MCAZTradeName,
"Manufacturer Status";
SQL SELECT APPLICANT,
CATEFORY,
FORM,
"GENERIC NAME",
MANUFACTURER,
"REGISTRATION NUMBER",
"stat_date",
STRENGTH,
"TRADE NAME",
"Manufacturer Status"
FROM PharmaMarketAudit.dbo.tblMCAZRegister;

PortfolioAge:
load

// Original Code by Haider Al-Seaidy. Thanks!
IF (Today() <= AddYears([stat_date],1),'0 Years ',
Age(Today(),[stat_date]) & ' Years ')
&
IF(MONTH(Today()) = MONTH([stat_date]),'0 Months ',
IF(DAY([stat_date]) < DAY(Today()), fabs(MONTH(Today())-Month([stat_date])) & ' Months ',
((MONTH(Today())+12)-Month(stat_date))-1 & ' Months '
))
&
IF(MONTH(Today()) = MONTH(stat_date),DAY(Today()) - DAY(stat_date) & ' Days',
IF(DAY(Today()) > DAY(stat_date),DAY(Today()) - DAY(stat_date) & ' Days',
DAY(DATE(Interval(Today() - stat_date)))-1 & ' Days'
)) As PortfolioAge

Resident MCAZAprroved;

I get an error message, Table not found.

Please assist

Regards.

Re: Calculating product portfolio age

ali_hijazi — Wed, 16 Mar 2016 11:34:57 GMT

either rename your Table as MCAZAprroved

or use resident [MCAZAprroved]

the brackets are part of the name of your table

Re: Calculating product portfolio age

Chanty4u — Wed, 16 Mar 2016 11:37:14 GMT

may be

mising the brakets of ur table name

Re: Calculating product portfolio age

MayilVahanan — Wed, 16 Mar 2016 11:39:44 GMT

Please check "PharmaMarketAudit.dbo.tblMCAZRegister" is available or not?

Can you post the error message screenshot?

Re: Calculating product portfolio age

sunny_talwar — Wed, 16 Mar 2016 12:11:51 GMT

I don't think square brackets matter here.

Although it would be helpful if you can post a screenshot of your error message

Re: Calculating product portfolio age

Anonymous — Wed, 16 Mar 2016 12:48:29 GMT

Many thanks, that solved my problem

Re: Calculating product portfolio age

Chanty4u — Wed, 16 Mar 2016 12:49:26 GMT

Re: Calculating product portfolio age

Anonymous — Wed, 16 Mar 2016 12:59:31 GMT

Many thanks Sunny, the issue was on brackets

Re: Calculating product portfolio age

Anonymous — Wed, 16 Mar 2016 12:59:59 GMT

Many thanks, issue was on brackets

Re: Calculating product portfolio age

Anonymous — Wed, 16 Mar 2016 13:01:00 GMT

Many thanks for your input, brackets was the issue

Re: Calculating product portfolio age

Chanty4u — Thu, 17 Mar 2016 08:04:33 GMT

Hmm cool