topic Re: Show value for current month in QlikView

Show value for current month

Anonymous — Tue, 12 Apr 2016 06:39:05 GMT

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.

Re: Show value for current month

sunny_talwar — Tue, 12 Apr 2016 08:05:06 GMT

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]))))))

Re: Show value for current month

qliksus — Tue, 12 Apr 2016 08:31:35 GMT

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

Re: Show value for current month

Anonymous — Tue, 12 Apr 2016 09:00:17 GMT

Thanks Sunny.

Re: Show value for current month

kkkumar82 — Tue, 12 Apr 2016 09:13:36 GMT

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

Re: Show value for current month

sunny_talwar — Tue, 12 Apr 2016 09:20:03 GMT

I am glad that you think so Kumar