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

# Problem with my set analysis Qlik Sense

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

• ###### Re: Problem with my set analysis Qlik Sense

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

Hi tresesco,

Thanks for your quick reply but I it didn't solve my problem

How can I add the year calculation inside the two previous months?

Thanks,

MB

• ###### Re: Problem with my set analysis Qlik Sense

I have modified my reply a bit above. Please check that if that helps. Else try to share a sample qvf/qvw.

• ###### Re: Problem with my set analysis Qlik Sense

I'll try it, thanks

• ###### Re: Problem with my set analysis Qlik Sense

Tried with no luck Tresesco

• ###### Re: Problem with my set analysis Qlik Sense

Sample qvw/qvf ?

• ###### Re: Problem with my set analysis Qlik Sense

Too big for upload, sorry .

Also I don't have permission to do share it.

• ###### Re: Problem with my set analysis Qlik Sense

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

Hi Balraj,

The solution you gave didn't work, sorry

I can't add lines in the script because I don't have the QVD's required for Reload the Script. Can someone give a solution that doesn't include creating new fields in the script and only changing the expression in the chart? Is it possible?

Thanks,

MB

• ###### Re: Problem with my set analysis Qlik Sense

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

)))