Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Good morning ladies and gentlemen of the QV Community
I have the following problem , I have a pivot table ( see attached image ) , which have a dimension cyclic group with three fields (Region , Sucurzal and Linea ) additionally have a dimension of a field ( _Etiqueta ) which is part an island with three registers ( Ventas, Presupuesto and Diferencia ) , this in order to achieve what you see in the image columns take the titles of expressions ( Ventas, Presupuesto and Diferencia) and columns table instance time expression:
Under this scenario , I have the problem that are highlighted in yellow (See the image), which shows that in 2014 month April , there is no Ventas and Presupuesto for M & MS "Linea" , for this reason not present information in the "Cierre día 09/04/2014" and "Act MTD 04/14" columns but must submit information in Act MTD Año Anterior 04/13
To accomplish this, the expresion is formed in the following manner
If(_Etiqueta = ‘Ventas’ , Sum ( { “Set Analisys para encontrar el año anterior”} VentasDS)
,If(_Etiqueta = ‘Presupuesto’, Sum ( { “Set Analisys para encontrar el año anterior”} Presupuesto)
,
(
Sum ( { “Set Analisys para encontrar el año anterior”} VentasDS)
/
Sum ( { “Set Analisys para encontrar el año anterior”} Presupuesto)
)-1
)
See the original Formula
If(_Etiqueta ='Ventas',
Money(
Sum({<Año_Calendario = {$(=if(GetSelectedCount(Año_Calendario) + GetSelectedCount(Mes_Calendario) + GetSelectedCount(Dia_Calendario) + GetSelectedCount(PeriodoJJ) =0, Year(Today()), Max(Año_Calendario)))}
>} ValorDS
)/1000000
),
If(_Etiqueta ='Presupuesto',
Money(
Sum({<Año_Calendario = {$(=if(GetSelectedCount(Año_Calendario) + GetSelectedCount(Mes_Calendario) + GetSelectedCount(Dia_Calendario) + GetSelectedCount(PeriodoJJ) =0, Year(Today())-1, Max(Año_Calendario)-1))}
>} presupuesto_General
)/1000000
),
Num(
(
Sum({<Año_Calendario = {$(=if(GetSelectedCount(Año_Calendario) + GetSelectedCount(Mes_Calendario) + GetSelectedCount(Dia_Calendario) + GetSelectedCount(PeriodoJJ) =0, Year(Today())-1, Max(Año_Calendario)-1))}
>} ValorDS
)
/
Sum({<Año_Calendario = {$(=if(GetSelectedCount(Año_Calendario) + GetSelectedCount(Mes_Calendario) + GetSelectedCount(Dia_Calendario) + GetSelectedCount(PeriodoJJ) =0, Year(Today())-1, Max(Año_Calendario)-1))}
>} presupuesto_General
)
)-1
,'#.##0,00%')
)
)
)
I solved the problem, with assitance of Miguel Angel Garcia from QlikFix, i change the IF Statement
I solved the problem, with assitance of Miguel Angel Garcia from QlikFix, i change the IF Statement