Format number problem in Rolling N months

[#1]

Hi Dear QV Community

I have this expresion to made a N rollling months in a Pivot Table with two dimmentions ([Indicadores productividad fideicomisos] and Periodo)

= If(Match(Mid([Indicadores productividad fideicomisos],1,5),'Total')=0,

Num(Sum({<Periodo= {">=$(=Date(addmonths(Max(Periodo), -vRolling), 'MMM-YY')) <=$(=Date(addmonths(Max(Periodo), 0),  'MMM-YY'))"}>} [Valor Indicadores productividad fideicomisos]),'#.##0%')

,Num(Sum({<Periodo= {">=$(=Date(addmonths(Max(Periodo), -vRolling), 'MMM-YY')) <=$(=Date(addmonths(Max(Periodo), 0),  'MMM-YY'))"}>} [Valor Indicadores productividad fideicomisos]),'#.##0')

)

I want format the result with #.##0% when dimention [Indicadores productividad fideicomisos]  is different to Total* and #.##0 when is equal to Total*

The expression works fine just for the selected period but not for rolling periods

[pover]

In the numbers tab of the pivot table properties window make sure the expression has its format set to "default expression".

Karl

[#3]

Hi Karl

I asgined default expression, but i believe that the problem are in the IF  clausule, becose  the iF part of the expresion acts in the  selected period, but not in the rolling periods.

I don't know how solve this problem.

[pover]

Strange.  You could try a pick() instead of an if() expression.

Karl

[#5]

How do I change the expression to peek?

= If(Match(Mid([Indicadores productividad fideicomisos],1,5),'Total')=0,

//Part IF

Num(Sum({<Periodo= {">=$(=Date(addmonths(Max(Periodo), -vRolling), 'MMM-YY')) <=$(=Date(addmonths(Max(Periodo), 0),  'MMM-YY'))"}>} [Valor Indicadores productividad fideicomisos]),'#.##0%')

//Part Else

,Num(Sum({<Periodo= {">=$(=Date(addmonths(Max(Periodo), -vRolling), 'MMM-YY')) <=$(=Date(addmonths(Max(Periodo), 0),  'MMM-YY'))"}>} [Valor Indicadores productividad fideicomisos]),'#.##0')

)

[pover]

Try

pick(wildmatch(mid([Indicadores productividad fideicomisos],1,5),'Total','*'),

Num(...,'#,##0%'),

Num(...,'#,##0')

)

Karl

[#7]

I tried with pick option but the rolling N months not works.

[#8]

This is the expression

pick(wildmatch(mid([Indicadores productividad fideicomisos],1,5),'Total','*'),

Num(Sum({<Periodo= {">=$(=Date(addmonths(Max(Periodo), -vRolling), 'MMM-YY')) <=$(=Date(addmonths(Max(Periodo), 0),  'MMM-YY'))"}>} [Valor Indicadores productividad fideicomisos]),'#.##0')

,Num(Sum({<Periodo= {">=$(=Date(addmonths(Max(Periodo), -vRolling), 'MMM-YY')) <=$(=Date(addmonths(Max(Periodo), 0),  'MMM-YY'))"}>} [Valor Indicadores productividad fideicomisos]),'#.##0,00%')

)

The pivot table just show the selected period, not rolling N months.

[pover]

In the image you included above, was your select only February 2012?

Karl

[#10]

Yes in the image included the Period selected are Feb-2012