Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Don't miss the upcoming Q&A with Qlik session on Qlik Application Automation on November 16th! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
asinghal0412
Partner
Partner

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/MonthNb. Of ProjectsMTDYTD
2021-Jan580%80%
2021-Feb650%63.63%
2021-Mar760% 
2021-Apr870% 
2021-May520% 
2021-Jun430% 
2021-Jul520% 
2021-Aug410% 
2021-Sep120% 
2021-Oct830% 
2021-Nov240% 
2021-Dec350%

 

   

 

 

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
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
Partner
Partner
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

Kushal_Chawda

@asinghal0412  please paste your expression which you are using

asinghal0412
Partner
Partner
Author

@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