topic Re: [ASK] show max date by expression in QlikView

[ASK] show max date by expression

rendiyan — Mon, 02 May 2016 10:00:33 GMT

Hello QlikView Master,

I have an issue for showing total of employee in each branch by employee branch period in that time, depend on user selection.

For example, user select on Year=2016 and Month=01

Then i have to show each employe that have period around user selection, like example below :

Branch	EmpID	Emp_Branch_End	count(Emp)
A001	E001	30/06/2016	1
A002	E002	15/01/2016	1
A002	E002	27/01/2016	1
A002	E003	28/02/2016	1
A003	E003	31/04/2016	1

Currently for expression i use :

count(distinct {<Emp_Branch_End={'>=$(=date(max(EndOfMonthSelection)))'},Year=,Month=>} EmpID)

But what i expected that result have to show latest employee branch like below :

Branch	EmpID	Emp_Branch_End	count(Emp)
A001	E001	30/06/2016	1
A002	E002	27/01/2016	1
A003	E003	31/04/2016	1

I've tried to change Emp_Branch_End with below calculated dimension :

IF(Emp_Branch_End=

aggr(

max(

Emp_Branch_End={'>=$(=date(max(EndOfMonthSelection)))'}

Year=,Month=

Emp_Branch_End),EmpID)

,Emp_Branch_End)

But the result come out isn't correct, some rows are compareable and show correct result, but some rows are null.

Is there any trick to solve this?

Best Regards,

Many Thanks,

Re: [ASK] show max date by expression

sunny_talwar — Mon, 02 May 2016 12:55:37 GMT

Try removing Emp_Branch_End date from the dimension and add it as an expression (Date(Max(Emp_Branch_End))). See if that helps or not

Re: [ASK] show max date by expression

rendiyan — Tue, 03 May 2016 04:20:53 GMT

Hi Sunny,

Thx for the response.

But if i add an expression as you suggested, it shows the same.

I've added screenshot below :

So description of the picture above are :

1. [Emp Branch End] is a dimension without calculation

2. [Emp Branch End Latest] dimension is using calculated aggr dimension :

aggr(

max(

Emp_Branch_End={'>=$(=date(max(EndOfMonthSelection)))'}

Year=,Month=

Emp_Branch_End),EmpID)

3. [current and max valid] is calculated dimension that compare result between [Emp Branch End] and [Emp Branch End Latest] , and result that i expected is it will show result 'y' for record two :

if(

[Emp Branch End]=

aggr(

max(

Emp_Branch_End={'>=$(=date(max(EndOfMonthSelection)))'}

Year=,Month=

Emp_Branch_End),EmpID)

,'y','n'

)

4. [Emp Branch End Exp] is an expression with your suggestion:

max(

Emp_Branch_End={'>=$(=date(max(EndOfMonthSelection)))'}

Year=,Month=

Emp_Branch_End)

is there any mistake that i've made?

=UPDATE=

I've tried to change[Emp Branch End] to TEXT(DATE,'YYYYMMDD')) in edit script and change max into maxstring, but the result is still the same.

i've created simulation in excel for [current and max valid], and it works.

I don't know what's wrong with my script.

Is there any solution?

many thx

Re: [ASK] show max date by expression

rendiyan — Wed, 04 May 2016 08:32:49 GMT

solve with edit my aggr script to :

aggr(NODISTINCT

max(

Emp_Branch_End={'>=$(=date(max(EndOfMonthSelection)))'}

Year=,Month=

Emp_Branch_End),EmpID)