If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.
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
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
@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
thanks for your support! But the code above shows 'error in the expression', I entered the proper field names, could you check the code?
@asinghal0412 please paste your expression which you are using
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'