Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
arasantorule
Creator III
Creator III

Help needed on previous year calculation

Dear Friends,

I am trying to calculate previous year's results as below.

if(GetSelectedCount(EffortYear)>0,

num(((num(sum({<EffortYear={'$(=Max(EffortYear)-1)'}>}Efforts),'#,##0.00'))/ (num(sum({<EffortYear={'$(=Max(EffortYear)-1)'}>} aggr(Count(DISTINCT  if(Aggr( sum({<EffortYear={'$(=Max(EffortYear)-1)'}>} Efforts),[Staff ID],MonthYear)>0, MonthYear)),[Staff ID],MonthYear))/Count(DISTINCT TOTAL {<EffortYear={'$(=Max(EffortYear)-1)'}>} MonthYear),'#,##0.00'))),'#,##0.00'),

num(((num(sum({<EffortYear={'$(=Max(EffortYear)-1)'}>}Efforts),'#,##0.00'))/ (num(sum({<EffortYear={'$(=Year(Today())-1)'}>} aggr(Count(DISTINCT  if(Aggr( sum({<EffortYear={'$(=Year(Today())-1)'}>} Efforts),[Staff ID],MonthYear)>0, MonthYear)),[Staff ID],MonthYear))/Count(DISTINCT TOTAL {<EffortYear={'$(=Max(EffortYear)-1)'}>} MonthYear),'#,##0.00'))),'#,##0.00'))

It works fine when no years or more than one year selected.

However, when we select only one year the above calculation gives no results. I have Tried all possibilities with no success.

Could you please help to fix the issue?

Thanks.

3 Replies
lironbaram
Partner - Master III
Partner - Master III

some questions

1. does the user male selection on the field EffortYear?

2.the cause might also be your if statement that doesn't have set analysis

arasantorule
Creator III
Creator III
Author

Dear Liron,

Yes. The user makes selection on the Effort Year.

Thanks

Silambarasan

arasantorule
Creator III
Creator III
Author

Dear Friends,

I have made changes to the expression as below.

if(GetSelectedCount(EffortYear)>0,num(((num(sum({<EffortYear={"$(=Max(EffortYear)-1)"}>}Efforts),'#,##0.00'))/

(num(sum({<EffortYear={"$(=Max(EffortYear)-1)"}>} aggr(Count({<EffortYear={"$(=Max(EffortYear)-1)"}>} DISTINCT  if(Aggr( sum({<EffortYear={"$(=Max(EffortYear)-1)"}>} Efforts),[Staff ID],MonthYear)>0, MonthYear)),[Staff ID],MonthYear))

/Count({<EffortYear={"$(=Max(EffortYear)-1)"}>}DISTINCT TOTAL {<EffortYear={"$(=Max(EffortYear)-1)"}>} MonthYear),'#,##0.00'))),'#,##0.00'),

num(((num(sum({<EffortYear={"$(=Year(Today())-1)"}>}Efforts),'#,##0.00'))/ (num(sum( aggr(Count(DISTINCT  if(Aggr( sum({<EffortYear={"$(=Year(Today())-1)"}>} Efforts),[Staff ID],MonthYear)>0, MonthYear)),[Staff ID],MonthYear))

/Count(DISTINCT TOTAL {<EffortYear={"$(=Year(Today())-1)"}>} MonthYear),'#,##0.00'))),'#,##0.00'))

Its working fine now.

Thanks

Silambarasan.