3 Replies Latest reply: May 4, 2016 4:32 AM by Rendi Yanuar RSS

    [ASK] show max date by expression

    Rendi Yanuar

      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,

        • Re: [ASK] show max date by expression
          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

            • Re: [ASK] show max date by expression
              Rendi Yanuar

              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
                  Rendi Yanuar

                  solve with edit my aggr script to :

                  aggr(NODISTINCT

                                 max(

                                      {<

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

                                           Year=,Month=

                                      >}

                       Emp_Branch_End),EmpID)