Cumalative YTD

[asinghal0412]

Dear Qlik Community, I am stuck in creating a YTD and need your help.

Please look at the table below, i have the Col 2 - Nb of Projects and Col 3 - MTD values. I need to calculate the YTD value

YTD = weighted average of MTD & Nb. of Projects

For ex - YTD for Jan-2021 = (80*5)/5 = 80%

YTD  for Feb - 2021 = (80*5 + 50*6)/(5+6) = 63.63% and so on

Year/Month	Nb. Of Projects	MTD	YTD
2021-Jan	5	80%	80%
2021-Feb	6	50%	63.63%
2021-Mar	7	60%
2021-Apr	8	70%
2021-May	5	20%
2021-Jun	4	30%
2021-Jul	5	20%
2021-Aug	4	10%
2021-Sep	1	20%
2021-Oct	8	30%
2021-Nov	2	40%
2021-Dec	3	50%

Can some1 help me to create the last column in set analysis using the YTD formula described above? in case of any questions feel free to reach out to me

Best,
Akash

[Kushal_Chawda]

@asinghal0412  try below

=rangesum(above(sum(MTD)*sum([Nb. Of Projects]),0,RowNo(total)))/
rangesum(above(sum([Nb. Of Projects]),0,RowNo(total)))

Note: Replace sum(MTD) & sum([Nb. Of Projects]) expression with your actual MTD and [Nb. Of Projects] expressions

[asinghal0412]

Dear Kushal,

thanks for your support! But the code above shows 'error in the expression', I entered the proper field names, could you check the code?

Thanks,

Akash

[Kushal_Chawda]

@asinghal0412  please paste your expression which you are using

[asinghal0412]

@Kushal_Chawda 

rangesum(above(sum([Project Launch Success - PEP])*sum(total Count([JIRA Link])),0,RowNo(total)))/
rangesum(above(sum(total count([JIRA Link])),0,RowNo(total)))

I added total to avoid Nested of aggregation

Count(Jira Link)  - Nb. of Projects

[Project Launch Success - PEP] - variable that peform MTD operation

It says -  'Error in expression'

Best,

Akash