topic Re: Help in accumulation formulas (Replacing above) in QlikView

Help in accumulation formulas (Replacing above)

cesaraccardi — Wed, 29 Aug 2012 18:34:46 GMT

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?

Re: Help in accumulation formulas (Replacing above)

Wed, 29 Aug 2012 18:47:47 GMT

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

Re: Help in accumulation formulas (Replacing above)

whiteline — Wed, 29 Aug 2012 18:54:51 GMT

You can add years to all of your sets:

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

Re: Help in accumulation formulas (Replacing above)

cesaraccardi — Wed, 29 Aug 2012 18:59:24 GMT

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:

Re: Help in accumulation formulas (Replacing above)

cesaraccardi — Wed, 29 Aug 2012 19:05:53 GMT

Thanks.

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

Re: Help in accumulation formulas (Replacing above)

whiteline — Wed, 29 Aug 2012 19:07:48 GMT

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

Re: Help in accumulation formulas (Replacing above)

cesaraccardi — Fri, 31 Aug 2012 13:29:45 GMT

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!