Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
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
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))
)))
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)
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
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
I have modified my reply a bit above. Please check that if that helps. Else try to share a sample qvf/qvw.
I'll try it, thanks
Tried with no luck Tresesco
Sample qvw/qvf ?
Too big for upload, sorry .
Also I don't have permission to do share it.
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