Problem with my set analysis Qlik Sense

[miguelbraga]

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.

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

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

[slribeiro · Accepted solution]

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))

                            )))