Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Format number problem in Rolling N months

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

pantallaso.png

9 Replies
pover
Honored Contributor

Format number problem in Rolling N months

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

Karl

Not applicable

Format number problem in Rolling N months

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
Honored Contributor

Format number problem in Rolling N months

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

Karl

Not applicable

Format number problem in Rolling N months

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
Honored Contributor

Format number problem in Rolling N months

Try

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

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

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

)

Karl

Not applicable

Format number problem in Rolling N months

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

Not applicable

Format number problem in Rolling N months

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
Honored Contributor

Format number problem in Rolling N months

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

Karl

Not applicable

Format number problem in Rolling N months

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

Community Browser