Show value for current month

[Anonymous]

Hi All,

If I select FA-JV(Function= FA-JV)

Leverage is not showing current month value(201). It is showing 174. If I click on Quarter or moth it is showing correct values. Please find sample data.  (For FA-JV No Partner)

(Leverage= Partner to Professional ratio )

Requirement is if I select in FA-JV Leverage wil show current month(Feb)  FA-JV Head count and if I select Quarter or Month  it will show that Quarter or Month  FA-JV Head count like 0:201.( For FA-JV No Partner)

My Expression:

=if(Function='FA-JV',0&':'&Round(Avg(Aggr(Count(DISTINCT([Emp. Id])), Month))),
if(GetSelectedCount([Fiscal Quarter]) > 0,1&':'&Round((Round(Avg(Aggr(Count(DISTINCT([Emp. Id])), Month)))- Round(Avg(Aggr(Count({<Grade={'Partner'}>}DISTINCT([Emp. Id])), Month))))
/Round(Avg(Aggr(Count({<Grade={'Partner'}>}DISTINCT([Emp. Id])), Month)))),
if(GetSelectedCount(Month)>0,1&':'&Round((Round(Avg(Aggr(Count(DISTINCT([Emp. Id])), Month)))- Count({<Grade={'Partner'}>}DISTINCT([Emp. Id])))/Count({<Grade={'Partner'}>}DISTINCT([Emp. Id]))),
1&':'&Round((Count({<CDate = {'02/01/2016'}>}DISTINCT([Emp. Id]))- Count({<Grade={'Partner'},CDate = {'02/01/2016'}>}DISTINCT([Emp. Id])))/Count({<Grade={'Partner'},CDate = {'02/01/2016'}>}DISTINCT([Emp. Id])))
)))

Thanks,

Krishna.

[sunny_talwar · Accepted solution]

Not 100% certain, but does this give you a solution you are looking for:

=If(Function = 'FA-JV',0&':'& Round(Count(DISTINCT {<CDate = {"$(=Date(Max(CDate)))"}>} [Emp. Id])),

If(GetSelectedCount([Fiscal Quarter]) > 0, 1 & ':'& Round((Round(Avg(Aggr(Count(DISTINCT([Emp. Id])), Month)))- Round(Avg(Aggr(Count({<Grade={'Partner'}>}DISTINCT([Emp. Id])), Month))))

/Round(Avg(Aggr(Count({<Grade={'Partner'}>}DISTINCT([Emp. Id])), Month)))),

if(GetSelectedCount(Month)>0,1&':'&Round((Round(Avg(Aggr(Count(DISTINCT([Emp. Id])), Month)))- Count({<Grade={'Partner'}>}DISTINCT([Emp. Id])))/Count({<Grade={'Partner'}>}DISTINCT([Emp. Id]))),

1&':'&Round((Count({<CDate = {"$(=Date(Max(CDate)))"}>}DISTINCT([Emp. Id]))- Count({<Grade={'Partner'},CDate = {"$(=Date(Max(CDate)))"}>}DISTINCT([Emp. Id])))/Count({<Grade={'Partner'},CDate = {"$(=Date(Max(CDate)))"}>}DISTINCT([Emp. Id]))))))

[qliksus]

Hi,

It showing the values correctly  as you haven't filtered the current month its showing the average of all months as 174

Jan	198
Feb	201
Apr	146
May	148
Jun	158
Jul	162
Aug	169
Sep	168
Oct	183
Nov	191
Dec	191
Average	174.0909091

So if you want the current  month value for the selection you will have to change your expression like something below

Round(Avg(Aggr(Count({<Month={'$(CM)'}>}DISTINCT([Emp. Id])), Month)))

[Anonymous]

Thanks Sunny.

[kkkumar82]

Sunny your are a real genious, appreciate you for your quick answers

[sunny_talwar]

I am glad that you think so Kumar