topic Re: Create column % from other column values in QlikView

Create column % from other column values

Tue, 15 Apr 2014 07:27:49 GMT

Hello,

I have a calculated expresion in the script to create a new column in my table. The calculate expresion is to obtain only the "Tipo actuacion" which finish in "1":

LOAD if(Right([Tipo actuacion],1)='1',[Tipo actuacion]) as [Notificaciones],

The problem is that when I calculate a % column in a Pivot Table, it takes in consideration the other values (those which don't finish in "1" which appear in blank in the column "Notificaciones"), so the percentages aren't as I want.

Re: Create column % from other column values

alexandros17 — Tue, 15 Apr 2014 07:30:56 GMT

Please send the expression that computes the % and the dimensions of your chart.

Re: Create column % from other column values

rustyfishbones — Tue, 15 Apr 2014 07:33:40 GMT

There is no need to use

LOAD if(Right([Tipo actuacion],1)='1',[Tipo actuacion]) as [Notificaciones],

Add Notificaciones as your dimension, and use the following as your expression

SUM({<Notificaciones = {'*1'}>}Cantidad)

and then use relative to get your percentage

Re: Create column % from other column values

tresB — Tue, 15 Apr 2014 07:33:51 GMT

Use set in your expression like:

Sum({<[Notificaciones]={1}>} YourField) / Sum({<[Notificaciones]={1}>} TOTAL YourField)

Re: Create column % from other column values

Tue, 15 Apr 2014 07:43:55 GMT

I can not use Relative to get the percentage, this is a Pivot Table

Re: Create column % from other column values

Tue, 15 Apr 2014 07:46:16 GMT

The dimensions are both calculated in the script, and are these:

- Notificaciones: if(Right([Tipo actuacion],1)='1',[Tipo actuacion])

- AñoExpediente: RIGHT(LEFT([Nº Expediente],7),4)

The expression that computes the % is:

((Sum(Cantidad))/Aggr(NODISTINCT Sum(Cantidad), [AñoExpediente]))*100

Re: Create column % from other column values

Tue, 15 Apr 2014 07:47:19 GMT

I have tested it but it doesn´t show the percentages in the table...

Re: Create column % from other column values

alexandros17 — Tue, 15 Apr 2014 07:51:10 GMT

Try this and let me know

((Sum({$ <Notificaciones={'1'}>} Cantidad))/Aggr(NODISTINCT Sum({$ <Notificaciones={'1'}>}Cantidad), [AñoExpediente]))*100

Re: Create column % from other column values

tresB — Tue, 15 Apr 2014 07:51:14 GMT

Goto number tab-> enable 'Show in Percent (%)'

Re: Create column % from other column values

Tue, 15 Apr 2014 07:56:27 GMT

It is enable

Re: Create column % from other column values

Tue, 15 Apr 2014 07:58:20 GMT

I have tried it, but it doesn´t appear anything in the cells

Re: Create column % from other column values

tresB — Tue, 15 Apr 2014 07:58:50 GMT

This long chain could be minimized, if you could share your sample app with sample data.

Re: Create column % from other column values

Tue, 15 Apr 2014 08:06:30 GMT

The problem is that I use Personal Edition, so I can´t open the file if you modify it

Re: Create column % from other column values

rustyfishbones — Tue, 15 Apr 2014 08:09:02 GMT

Apologies

Then say

SUM({<Notificaciones = {'*1'}>}Cantidad)/SUM(TOTAL {<Notificaciones = {'*1'}>}Cantidad)

or change Cantidad for your expression field

Regards

Alan

Re: Create column % from other column values

rustyfishbones — Tue, 15 Apr 2014 08:10:11 GMT

The result would be like this

Re: Create column % from other column values

tresB — Tue, 15 Apr 2014 08:11:51 GMT

Let us have a look to find the actual issue, we could tell you then what to do.

Re: Create column % from other column values

Tue, 15 Apr 2014 08:25:20 GMT

Solved,

The solution was this, but adding * before the 1:

((Sum({$ <Notificaciones={'*1'}>} Cantidad))/Aggr(NODISTINCT Sum({$ <Notificaciones={'*1'}>}Cantidad), [AñoExpediente]))*100

Now, I want to roung this percentage to 2 decimals

Re: Create column % from other column values

alexandros17 — Tue, 15 Apr 2014 08:37:13 GMT

Delete the part *100 go in numbers, set decimal (2 digits) and check the box show in %

Re: Create column % from other column values

Tue, 15 Apr 2014 08:42:12 GMT

Perfect! Thanks a lot!