expression for accumulated years

Wed, 25 Nov 2020 16:16:04 GMT

Hello Everyone,

I am a newbie to QlikView. I have requirement to calculate value based on certain criteria.

below is the input table in the QlikView

conditions:

for each month in the same year

if(Jan) -> sum of RiskCategory weights in Jan of the same year

if(Feb) --> sum of RiskCategory weights in Jan and Feb of the same year

if(March)-- > sum of RiskCategory weights from Jan to March of the same year

if(April) --> sum of RiskCategory weights from Jan to April of the same year

if(Dec) --> sum of RiskCategory weights from January to December of the same year

**if multiple RiskCategories existed for any month we should take one occurrence of risk category which is having maximum risk weight

For example if we want to calculate riskweight for the month of November in 2016 then we should consider the below rows only

Month

Year

RiskCategory

RiskWeight

XYZ	October	2016	Cardiovascular	0.649	1st occurrence
XYZ	October	2016	Pulmonary	0.341	1st occurrence
XYZ	November	2016	Cardiovascular	0.649	2nd occurrence
XYZ	November	2016	Diabetes type 2	0.666
XYZ	November	2016	Psychiatric	0.798
XYZ	November	2016	Pulmonary	1.037	2nd occurrence
XYZ	November	2016	Renal	1.896

** since I do not have data from January to September in 2016 i have considered only October and November data for November month calculation

now the result should be (0.649 for Cardiovascular+ 1.037 for Pulmonary + 0.666 for Diabetes type 2 + 0.798 for Psychiatric + 1.896 for Renal ) + constant value of 0.536 = 5.582

and the result table should be

Please let me know if this is possible through the expression and help me to solve the logic

PS: there multiple ID's will be existed in the table

topic expression for accumulated years in QlikView

expression for accumulated years