Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

arasantorule
Contributor 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
Honored Contributor II

Re: Help needed on previous year calculation

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
Contributor III

Re: Help needed on previous year calculation

Dear Liron,

Yes. The user makes selection on the Effort Year.

Thanks

Silambarasan

arasantorule
Contributor III

Re: Help needed on previous year calculation

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.