10 Replies Latest reply: Jan 14, 2016 6:19 AM by Simão Ribeiro RSS

    Problem with my set analysis Qlik Sense

    Miguel Braga

      Greeting experts,

       

      I have a problem with my expression in a chart expression that I really need to solve. In the following images represents my problem:

       

      With [Ano] field selected as '2016' and [Mês] selected as 'Jan' appear the month selected but don't appear the two months before the selected one.

      Screenshot.png

      With [Ano] field selected as '2015' and [Mês] selected as 'Jan' appears what is expected.

      Screenshot1.png

       

      My chart expression with set analysis:

       

      sum({$<[cto_tipo_contrato_PCO]={"Renting"}

        ,ano_PCO = {$(=max(ano_PCO))}

               ,dsc_mes_PCO={$(=month(AddMonths(max(MMMYYYY_PCO), -2)))

          , $(=month(AddMonths(max(MMMYYYY_PCO),-1)))

          , $(=month(AddMonths(max(MMMYYYY_PCO),0))                    

                                  )

        }>} cto_valor_prod_mkt)

       

      • ano_PCO - year field
      • dsc_mes_PCO - month field
      • cto_valor_prod_mkt - my values

       

      My question is, how can I by selecting the month 'Jan' and year '2016' get the values of 'Dec 2015' and 'Nov 2015'? What I'm doing wrong in my expression. Any help?

       

      Thanks,

      MB

        • Re: Problem with my set analysis Qlik Sense
          Tresesco B

          Year restriction  'ano_PCO = {$(=max(ano_PCO))}' is causing the issue. This should be dealt with Date field rather than Month field.

           

          try something like:

          sum({$<[cto_tipo_contrato_PCO]={"Renting"},

          YearField, MonthField,

            ,DateField={'>=$(=AddMonths(Max(Datefield)),-3) <=$(=Date(Max(DateField)))'}

                                      )

            }>} cto_valor_prod_mkt)

          • Re: Problem with my set analysis Qlik Sense
            balraj ahlawat

            may be like this?

             

            =sum({$<[cto_tipo_contrato_PCO]={'Renting'} , ano_PCO = {'=$(=max(ano_PCO))'},

            dsc_mes_PCO={$(=month(AddMonths(max(MMMYYYY_PCO), -2))), $(=month(AddMonths(max(MMMYYYY_PCO),-1)))

                , $(=month(AddMonths(max(MMMYYYY_PCO),0)))} >} cto_valor_prod_mkt)

             

            Make sure value in dsc_mes_PCO should be numeric as month() returns an integer

            • Re: Problem with my set analysis Qlik Sense
              Simão Ribeiro

              if(

              (GetFieldSelections(dsc_mes_PCO)='Jan')

              ,

              (sum({$<[cto_tipo_contrato_PCO]={"Renting"}                       

                ,ano_PCO = {$(=max(ano_PCO))}

                      ,dsc_mes_PCO= {$(=month(AddMonths(max(date(MMMYYYY_PCO)),0)))}>+

                    <[cto_tipo_contrato_PCO]={"Renting"}                       

                ,ano_PCO = {$(=max(ano_PCO)-1)}

                      ,dsc_mes_PCO= {$(=month(AddMonths(max(date(MMMYYYY_PCO)),-1)))}>+

                    <[cto_tipo_contrato_PCO]={"Renting"}                       

                ,ano_PCO = {$(=max(ano_PCO)-1)}

                      ,dsc_mes_PCO= {$(=month(AddMonths(max(date(MMMYYYY_PCO)),-2)))}>

                                         } cto_valor_prod_mkt))                        

              ,

              (if(

              (GetFieldSelections(dsc_mes_PCO)='Fev')

              ,

              (sum({$<[cto_tipo_contrato_PCO]={"Renting"}                       

                ,ano_PCO = {$(=max(ano_PCO))}

                      ,dsc_mes_PCO= {$(=month(AddMonths(max(date(MMMYYYY_PCO)),0)))}>+

                    <[cto_tipo_contrato_PCO]={"Renting"}                       

                ,ano_PCO = {$(=max(ano_PCO))}

                      ,dsc_mes_PCO= {$(=month(AddMonths(max(date(MMMYYYY_PCO)),-1)))}>+

                    <[cto_tipo_contrato_PCO]={"Renting"}                       

                ,ano_PCO = {$(=max(ano_PCO)-1)}

                      ,dsc_mes_PCO= {$(=month(AddMonths(max(date(MMMYYYY_PCO)),-2)))}>

                                         } cto_valor_prod_mkt))

              ,

              (sum({$<[cto_tipo_contrato_PCO]={"Renting"}                       

                ,ano_PCO = {'=$(=max(ano_PCO))'}

                      ,dsc_mes_PCO={$(=month(AddMonths(max(date(MMMYYYY_PCO)), -2)))

                  , $(=month(AddMonths(max(date(MMMYYYY_PCO)),-1)))

                  , $(=month(AddMonths(max(date(MMMYYYY_PCO)),0))                    

                                          )

                }>} cto_valor_prod_mkt))

                                          )))