New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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.

Thanks.

3 Replies
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

Contributor III

Re: Help needed on previous year calculation

Dear Liron,

Yes. The user makes selection on the Effort Year.

Thanks

Silambarasan

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.