topic Re: Using the Below () statement in QlikView

Using the Below () statement

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

Hi.

Mainly I have a table with the CESADOS and ACTIVOS columns.

Then we divide CESADOS / ACTIVOS.

But there was a case, that CESADO has to go back one row to do the calculation CESADO / ACTIVOS.Use the BELOW () sentence,

to go back a row in CESADO.

So far so good, but when I select a day, no value appears.

select:

Mes: ene

Día:4

You must obtain.

I am using the BELOW () statement so that the values of CESADOS, go back one day or row.

Please help me get that value when I select a day.

Like when I select a Month, I would like to obtain the average of Assets, Cesados and the percentage.

Mes = 'ene'

The average of

CESADOS = 49

ACTIVOS = 8291

Below(CESADO)/ACTIVO = 0.60%

I attach the Qvw y Xls.

Re: Using the Below () statement

sunny_talwar — Fri, 19 Jan 2018 20:41:20 GMT

Try this

Below(Only({<Mes, Día>}Cesado))/Only({<Mes, Día>}Activos) * Avg(1)

Removing sort by expression on the sort tab... or ignore selection in sort expressions also

Re: Using the Below () statement

cristianj23a — Mon, 22 Jan 2018 17:02:09 GMT

Hi Sunny.

Sorry for posting several times.

Thanks, it works.

But not in the Real Application.

The real logic for Cesado is.

IF(GetSelectedCount (Día)=1,

Below(

(COUNT({<%FILTRO_ACTIVOS={'CESADO'},[Motivo Cese]={'*'}-{''}>} [PA0000.Número de personal_PERNR]))

-(COUNT({<%FILTRO_ACTIVOS={'CESADO'},%flag_4d_abs={'1'},[Motivo Cese]>}[PA0000.Número de personal_PERNR]))

)

Avg(

AGGR(

Below(

(COUNT({<%FILTRO_ACTIVOS={'CESADO'},[Motivo Cese]={'*'}-{''}>} [PA0000.Número de personal_PERNR]))

-(COUNT({<%FILTRO_ACTIVOS={'CESADO'},%flag_4d_abs={'1'},[Motivo Cese]>}[PA0000.Número de personal_PERNR]))

),PA0000.Fecha))//COUNT(distinct {<DíaSemana={'*'}-{'dom'}>} PA0000.Fecha)

)

that logic helps me to go back one row in the table.

When I select a day, the information is deleted.

Is there any logic that I keep that value in the table, when I select a day?

I'm also trying to display the values in a text object.

Greetings.

Attached the Qvw,

Re: Using the Below () statement

sunny_talwar — Mon, 22 Jan 2018 17:16:31 GMT

Check attached for table...

For the text box object... what would you want to see when nothing is selected?

Re: Using the Below () statement

cristianj23a — Mon, 22 Jan 2018 18:56:31 GMT

Thanks Sunny.

In the qvw that you attach, it is a summary of the true application.

The analysis is carried out annually, monthly and daily.

If you do not select anything, the cessation of each year should appear.

The logic is similar to the calculation of assets (The calculation is a daily average), only that in cesados they go back a day or a row and that does not work for me.

Re: Using the Below () statement

sunny_talwar — Mon, 22 Jan 2018 19:03:45 GMT

Try this

=Num(Avg(Aggr(

IF(GetSelectedCount (Día)=1,

Below(

(COUNT({<%FILTRO_ACTIVOS={'CESADO'},[Motivo Cese]={'*'}-{''}, Mes, Día>} [PA0000.Número de personal_PERNR]))

-(COUNT({<%FILTRO_ACTIVOS={'CESADO'},%flag_4d_abs={'1'},[Motivo Cese], Mes, Día>}[PA0000.Número de personal_PERNR]))

) * Avg(1)

Avg(

AGGR(

Below(

(COUNT({<%FILTRO_ACTIVOS={'CESADO'},[Motivo Cese]={'*'}-{''}, Mes, Día>} [PA0000.Número de personal_PERNR]))

-(COUNT({<%FILTRO_ACTIVOS={'CESADO'},%flag_4d_abs={'1'},[Motivo Cese], Mes, Día>}[PA0000.Número de personal_PERNR]))

),PA0000.Fecha))//COUNT(distinct {<DíaSemana={'*'}-{'dom'}>} PA0000.Fecha)

)

IF(GetSelectedCount (Día)=1,

(

(COUNT(distinct {<PA0000.DíaSemana-={'dom'},%FILTRO_ACTIVOS={'ACTIVO'},[Tipo Ausentismo],SEDE-={' '}, Mes, Día>} [PA0000.Número de personal_PERNR]))-

sum({<Mes, Día>}aggr(

sum({<Mes, Día>}if(PA0000.Fecha>=date([PA2001.Inicio de validez_BEGDA]+4,'DD/MM/YYYY') and PA0000.DíaSemana<>'dom' and %FILTRO_ACTIVOS='ACTIVO' and SEDE<>' ',

[PA2001.EXP1_Días presenc./abs._ABWTG]))

,[PA0000.Número de personal_PERNR],PA0000.Fecha,[PA2001.Inicio de validez_BEGDA]))

)

sum({<Mes, Día>}aggr(

((COUNT(distinct {<PA0000.DíaSemana-={'dom'},%FILTRO_ACTIVOS={'ACTIVO'},[Tipo Ausentismo],SEDE-={' '}, Mes, Día>} [PA0000.Número de personal_PERNR]))-

sum({<Mes, Día>}aggr(

sum({<Mes, Día>if(PA0000.Fecha>=date([PA2001.Inicio de validez_BEGDA]+4,'DD/MM/YYYY') and PA0000.DíaSemana<>'dom' and %FILTRO_ACTIVOS='ACTIVO' and SEDE<>' ',

[PA2001.EXP1_Días presenc./abs._ABWTG]))

,[PA0000.Número de personal_PERNR],PA0000.Fecha,[PA2001.Inicio de validez_BEGDA])))

,PA0000.Fecha))

/count(distinct {<PA0000.DíaSemana-={'dom'}, Mes, Día>} PA0000.Fecha))

, Mes, Día)), '##.##%')

Without selection

With Selection

Re: Using the Below () statement

cristianj23a — Tue, 23 Jan 2018 18:21:17 GMT

Tanks You Sunny.

One last query.

Is it possible to get those values but divided by SEDE,Und.OrganizativaDesc,[Tipo Empleado Desc] and others dimesiones? Example.

This problem happened to me from the beginning.

In these two graphical data, the same information should appear but divided by SEDE,Und.OrganizativaDesc,[Tipo Empleado Desc]

Re: Using the Below () statement

sunny_talwar — Tue, 23 Jan 2018 18:27:19 GMT

Don't think I follow?

Re: Using the Below () statement

cristianj23a — Tue, 23 Jan 2018 18:42:03 GMT

I got stuck with this topic. Please help me this part to close.

Re: Using the Below () statement

sunny_talwar — Tue, 23 Jan 2018 18:45:14 GMT

But I am not sure I understand what you are looking to get