3 Replies Latest reply: Mar 3, 2016 1:52 AM by Chanchal Sharma RSS

    Set Analysis behaving differently with same syntax in qliksense

    Chanchal Sharma

      I have an expression:

      = sum({< CommonDate ={">=$(vCurrentMonthStart) <= $(vCurrentMonthEnd)"}>}ActivationQty)

      Which is giving me the desired result on the selected month name, however the below expression is not giving the result instead it gives me 0 as an output.

      Need urgent help.

      = sum({< CommonDate ={">=$(vPreviousMonthStart) <= $(vPreviousMonthEnd)"}>}ActivationQty)

       

      vCurrentMonthStart =Floor(Monthstart(Max(Activation_date)))

      vCurrentMonthEnd =Floor(MonthEnd(Max(Activation_date)))

      vPreviousMonthStart =Floor(Monthstart(Addmonths(Max(Activation_date),-1)))

      vPreviousMonthEnd =Floor(Monthend(Addmonths(Max(Activation_date),-1)))


      CommonDate is converted to number format.


      Please help.

        • Re: Set Analysis behaving differently with same syntax in qliksense
          Sunny Talwar

          You need to add Date() function to your variables so that it matches the format for CommonDate.

           

          vCurrentMonthStart =Date(Floor(Monthstart(Max(Activation_date))), 'CommonDateFieldFormat')

          vCurrentMonthEnd =Date(Floor(MonthEnd(Max(Activation_date))), 'CommonDateFieldFormat')

          vPreviousMonthStart =Date(Floor(Monthstart(Addmonths(Max(Activation_date),-1))), 'CommonDateFieldFormat')

          vPreviousMonthEnd =Date(Floor(Monthend(Addmonths(Max(Activation_date),-1))), 'CommonDateFieldFormat')

          • Re: Set Analysis behaving differently with same syntax in qliksense
            Sandeep Sarawgi

            I think your approach looks right overall.

             

            I would suggest not changing the dates into number.  I created a similar model in Qlik and seem to get the right results.

             

            vCurrentMonthStart =Monthstart(Max(Activation_date))

            vCurrentMonthEnd =MonthEnd(Max(Activation_date))

            vPreviousMonthStart =Monthstart(Addmonths(Max(Activation_date),-1))

            vPreviousMonthEnd =Monthend(Addmonths(Max(Activation_date),-1))


            On a separate though related note, while the expression sum({< CommonDate ={">=$(vPreviousMonthStart) <= $(vPreviousMonthEnd)"}>}ActivationQty) does the job, when in doubt you could also try evaluating the three components and checking separately in text boxes (see below).  Also you could create an expression using the three below instead of the more compact expression when in doubt.

            =sum(ActivationQty)

            =sum({<CommonDate ={"<=$(vPreviousMonthStart )"}>}ActivationQty)

            =sum({<CommonDate ={">=$(vPreviousMonthEnd )"}>}ActivationQty)


            • Re: Set Analysis behaving differently with same syntax in qliksense
              Chanchal Sharma

              Hi all,

               

              It worked fine with the expressions below.

              I created a field Monthname(CommonDate) as Month_Activation

              = sum({< CommonDate ={">=$(vPreviousMonthStart) <= $(vPreviousMonthEnd)"},Month_Activation= >}ActivationQty)

              = sum({< CommonDate ={">=$(vCurrentMonthStart) <= $(vCurrentMonthEnd)"},Month_Activation= >}ActivationQty)

               

              Variables:

              vCurrentMonthStart =Floor(Monthstart(Max(Activation_date)))

              vCurrentMonthEnd =Floor(MonthEnd(Max(Activation_date)))

              vPreviousMonthStart =Floor(Monthstart(Addmonths(Max(Activation_date),-1)))

              vPreviousMonthEnd =Floor(Monthend(Addmonths(Max(Activation_date),-1)))