Announcements
cancel
Showing results for
Did you mean:
Partner - Creator

## Cumalative YTD

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

4 Replies

@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

Partner - Creator
Author

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

Partner - Creator
Author

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