Help in accumulation formulas (Replacing above)

[cesaraccardi]

I have a straight table with the KPI values by Month and Year, I want to show in this same table some expressions:

1. The variation between the current Month and the Previous

2. The accumulated variations (1) along the current year

3. The variation between the current Month and the same Month on the last Year (12 Months variation)

I've already built all the expressions, however the last one makes the table show all the years I have in my table because is using 1 as the set identifier. How can I replace this and keep only the selected period with the correct accumulations?

Expression 3 disabled:

Expression 3 enabled:

Expression 3 formula:

if(
          sum({1<[Descrição do Subindicador]={'CUB-PR'}>} Valor)>0,
          if(
                    IsNull(above(total sum({1<[Descrição do Subindicador]={'CUB-PR'}>} Valor))),
                    0,
                    ((
                              sum({1<[Descrição do Subindicador]={'CUB-PR'}>} Valor)
                              /
                              above(total sum({1<[Descrição do Subindicador]={'CUB-PR'}>} Valor),12,0)
                    -1)*100
                    ) 
          )
)

Can someone help me please?

[#2]

Try using Monthname Functions with AddMonths(Date,-12) You could use this in SET to avoid using above

[whiteline]

You can add  years to all of your sets:

{1<[Descrição do Subindicador]={'CUB-PR'}, Year=P({$}Year)>}

[cesaraccardi]

Hi,

thanks for the quick response...  Like this?

sum({$<[Descrição do Subindicador]={'CUB-PR'},Ano={"$(=Year(AddMonths(Date#([Ano Mês],'YYYY/MMM'),-12)))"}>} Valor)

If put the modifier expression in a new the result appears to be correct (last year), however the expression "12 Meses" doesn't show any value:

[cesaraccardi]

Thanks.

I tried this.... but I need to calculate the variation with a year (2010) that is outside of my selection. Any ideas?

[whiteline]

I think, you can always use Only() over your expression with corresponding set expression.

[cesaraccardi · Accepted solution]

Hi,

The problem was solved.. I used the Only() in the expression with AddMonths like you said however for this to work I must have 1 select value for year, as my client agreed with this, so that's ok.

Thanks for your help!