Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello
I´ve been looking for this answer into the community and there are some similar topics but there are not a complete answer at all.
In addition of the partial sum, I need to show the % of the total sum
Ingresos, Costos, Gastos and Utilidad are expressions as a result of a algebraic operation from my database. As you can see, INGRESOS is the base to calculate all the others (COSTOS, GASTOS and UTILIDAD)
I will appreciate your help
Accepted Solutions
Is this what you want?
I will try to go step by step, but let me know if you still are not clear about something
1) Add an inline load at the end of your script.
Dim:
LOAD * Inline [
Dim
1
2
3
];
Modify your chart like this
Dimensions
Dim 1 change from Mes to
=Pick(Dim, MES, 'Total', 'Percent')
Dim 2 change from Clase to
=Pick(Dim, Clase, Dual(' ', 1), Dual(' ', 2))
Expressions changed to theses
1) Pick(Dim,
Num(Sum({<Descrip_Nivel_1 = {'INGRESOS'}>} Valor), '$ #.##0'),
Num(Sum({<Descrip_Nivel_1 = {'INGRESOS'}>} Valor), '$ #.##0'),
Num(1, '##,0%'))
2) Pick(Dim,
Num(Sum({<Descrip_Nivel_1 = {'COSTOS DE PRODUCCION O DE OPERACION'}>} Valor), '$ #.##0'),
Num(Sum({<Descrip_Nivel_1 = {'COSTOS DE PRODUCCION O DE OPERACION'}>} Valor), '$ #.##0'),
Num(Sum({<Descrip_Nivel_1 = {'COSTOS DE PRODUCCION O DE OPERACION'}>} Valor)/Sum({<Descrip_Nivel_1 = {'INGRESOS'}>} Valor), '##,0%'))
3) Pick(Dim,
Num(Sum({<Descrip_Nivel_1 = {'GASTOS'}>} Valor), '$ #.##0'),
Num(Sum({<Descrip_Nivel_1 = {'GASTOS'}>} Valor), '$ #.##0'),
Num(Sum({<Descrip_Nivel_1 = {'GASTOS'}>} Valor)/Sum({<Descrip_Nivel_1 = {'INGRESOS'}>} Valor), '##,0%'))
4) Pick(Dim,
Num(INGRESOS+COSTOS+GASTOS, '$ #.##0'),
Num(INGRESOS+COSTOS+GASTOS, '$ #.##0'),
Num(INGRESOS+COSTOS+GASTOS/Sum({<Descrip_Nivel_1 = {'INGRESOS'}>} Valor), '##,0%'))
Some other changes
1) Uncheck partial sum for Mes dimension
2) Added a color formatting for background color for the 2 dimensions and all the 4 expressions
Expression used for color
=If(Match(Dim, 2, 3), RGB(192,192,192))
Expression used for Format
=If(Match(Dim, 2, 3), '<B>')
Once you do all the above, you should almost reach what I have posted above. Let me know if you still have issues. Also, I know you are working with personal addition of QlikView, I will still attach the sample as it may help somebody else in the future.
Best,
Sunny
How about this:
INGRESOS: 1
COSTOS: Sum(if(Descrip_Nivel_1='COSTOS DE PRODUCCION O DE OPERACION', Valor))/Sum(if(Descrip_Nivel_1='INGRESOS', Valor))
GASTOS: Sum(if(Descrip_Nivel_1='GASTOS', Valor))/Sum(if(Descrip_Nivel_1='INGRESOS', Valor))
UTILIDAD: Sum(if(Descrip_Nivel_1='GASTOS', Valor))/Sum(if(Descrip_Nivel_1='INGRESOS', Valor))
Also, I believe that you can simplify your expressions using set analysis instead of using if statement. For example INGRESOS can be like this
Sum({<Descrip_Nivel_1 *= {'INGRESOS'}>} Valor)
instead of what you have today. I did not offer the change above because I am not sure if there is a reason you used if instead of set analysis 
Sunny, thank you very much
You are right about using set analysis instead of IF statement.
By the way, I used the expressions you suggested, and this is the result
Maybe there is something I am not doing well at all. I need to put the result under the $ values row.