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