Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
rendiyan
Partner - Creator
Partner - Creator

[ASK] show max date by expression

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 :

BranchEmpIDEmp_Branch_Endcount(Emp)
A001E00130/06/20161
A002E00215/01/20161
A002E00227/01/20161
A002E00328/02/20161
A003E00331/04/20161

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 :

BranchEmpIDEmp_Branch_Endcount(Emp)
A001E00130/06/20161
A002E00227/01/20161
A003E00331/04/20161

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,

3 Replies
sunny_talwar

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

rendiyan
Partner - Creator
Partner - Creator
Author

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

rendiyan
Partner - Creator
Partner - Creator
Author

solve with edit my aggr script to :

aggr(NODISTINCT

               max(

                    {<

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

                         Year=,Month=

                    >}

     Emp_Branch_End),EmpID)