topic Re: Pivot Table Partial Percentage in QlikView

Pivot Table Partial Percentage

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Pivot Table Partial Percentage

sunny_talwar — Wed, 16 Nov 2016 16:37:44 GMT

What are the expressions you are using here?

Re: Pivot Table Partial Percentage

Anil_Babu_Samineni — Wed, 16 Nov 2016 16:49:57 GMT

Copy paste those expressions and check Relative from Expression Tab which you need % and then see how works / Else As Sunny T asked, You may share those

Re: Pivot Table Partial Percentage

Anonymous — Wed, 16 Nov 2016 16:54:55 GMT

Following the expressions:

INGRESOS sum(if(Descrip_Nivel_1='INGRESOS', Valor))

COSTOS sum(if(Descrip_Nivel_1='COSTOS DE PRODUCCION O DE OPERACION', Valor))

GASTOS sum(if(Descrip_Nivel_1='GASTOS', Valor))

UTILIDAD INGRESOS+COSTOS+GASTOS

thanks a lot

Re: Pivot Table Partial Percentage

Anonymous — Wed, 16 Nov 2016 17:04:40 GMT

Anil Babu, thank you for your answer.

In this case I think is necesary to change from pivot table to simple table in order to get activated the Relative check box. Anyway I can´t get the result.

Re: Pivot Table Partial Percentage

sunny_talwar — Wed, 16 Nov 2016 17:23:33 GMT

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

Re: Pivot Table Partial Percentage

Anonymous — Thu, 17 Nov 2016 12:44:39 GMT

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.

Re: Pivot Table Partial Percentage

sunny_talwar — Thu, 17 Nov 2016 12:59:59 GMT

So the total row should be in percentage? Would you be able to share a sample where we can work on getting your requirement accomplished?

Re: Pivot Table Partial Percentage

Anonymous — Thu, 17 Nov 2016 13:05:02 GMT

Yes, exactly, a row like a total. The example would be like as the image I attached at first.

Re: Pivot Table Partial Percentage

sunny_talwar — Thu, 17 Nov 2016 13:17:03 GMT

I meant sample qvw

Preparing examples for Upload - Reduction and Data Scrambling

Re: Pivot Table Partial Percentage

Anonymous — Thu, 17 Nov 2016 14:03:48 GMT

Excuse me.

Can´t see how to attach a file. I see icons to attach an image, videos and links

Re: Pivot Table Partial Percentage

sunny_talwar — Thu, 17 Nov 2016 14:04:21 GMT

Uploading a Sample

Re: Pivot Table Partial Percentage

Anonymous — Thu, 17 Nov 2016 14:33:18 GMT

Uf!! It is really a trick

Thanks a lot again.

I have a personal edition, so I could not open a .qvw from others

Re: Pivot Table Partial Percentage

sunny_talwar — Thu, 17 Nov 2016 15:03:25 GMT

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

];

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(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)/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)/Sum({<Descrip_Nivel_1 = {'INGRESOS'}>} Valor), '##,0%'))

4) Pick(Dim,

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

Re: Pivot Table Partial Percentage

Anonymous — Thu, 17 Nov 2016 15:44:01 GMT

Great!! Thank you so much Sunny T

The 4th expression is not working. Maybe it is missing a quotation mark, function or something like that.

Re: Pivot Table Partial Percentage

Anonymous — Thu, 17 Nov 2016 16:00:12 GMT

Now all the expressions are working Ok. I added a 4th Dim in the script:

Dim:

LOAD * Inline [

Dim

];

Best regards

Re: Pivot Table Partial Percentage

sunny_talwar — Thu, 17 Nov 2016 16:08:33 GMT

Not sure why you need 4th Dim.