topic Format number problem in Rolling N months in QlikView

Format number problem in Rolling N months

Tue, 27 Mar 2012 18:36:38 GMT

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

Format number problem in Rolling N months

pover — Tue, 27 Mar 2012 21:59:14 GMT

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

Karl

Format number problem in Rolling N months

Tue, 27 Mar 2012 22:13:31 GMT

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.

Format number problem in Rolling N months

pover — Tue, 27 Mar 2012 22:17:16 GMT

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

Karl

Format number problem in Rolling N months

Tue, 27 Mar 2012 22:26:08 GMT

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

)

Format number problem in Rolling N months

pover — Wed, 28 Mar 2012 16:46:32 GMT

Try

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

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

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

)

Karl

Format number problem in Rolling N months

Wed, 28 Mar 2012 17:08:10 GMT

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

Format number problem in Rolling N months

Wed, 28 Mar 2012 17:13:04 GMT

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.

Format number problem in Rolling N months

pover — Wed, 28 Mar 2012 17:47:09 GMT

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

Karl

Format number problem in Rolling N months

Wed, 04 Apr 2012 12:44:47 GMT

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