Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
vmoreno2605
New Contributor III

RowNo Set analysis

Hi everyone! 

I have a large code, here is an example:

If(RowNo()=1, $(vInicioPlantillaCNDistrital),
if(RowNo()=2,
$(vInicioPlantillaCNDistrital)+
Count({<[Año]={$(vAño)},HireTerm={'H'},MesN={"<2"}>}Distinct total EMPLOYEE)
-Count({<[Año]={$(vAño)},HireTerm={'T'},MesN={"<2"}>}Distinct total EMPLOYEE),
if(RowNo()=3,
$(vInicioPlantillaCNDistrital)+
Count({<[Año]={$(vAño)},HireTerm={'H'},MesN={"<3"}>}Distinct total EMPLOYEE)
-Count({<[Año]={$(vAño)},HireTerm={'T'},MesN={"<3"}>}Distinct total EMPLOYEE),
if(RowNo()=4,
$(vInicioPlantillaCNDistrital)+
Count({<[Año]={$(vAño)},HireTerm={'H'},MesN={"<4"}>}Distinct total EMPLOYEE)
-Count({<[Año]={$(vAño)},HireTerm={'T'},MesN={"<4"}>}Distinct total EMPLOYEE),
if(RowNo()=5,
$(vInicioPlantillaCNDistrital)+
Count({<[Año]={$(vAño)},HireTerm={'H'},MesN={"<5"}>}Distinct total EMPLOYEE)
-Count({<[Año]={$(vAño)},HireTerm={'T'},MesN={"<5"}>}Distinct total EMPLOYEE),
if(RowNo()=6,
$(vInicioPlantillaCNDistrital)+
Count({<[Año]={$(vAño)},HireTerm={'H'},MesN={"<6"}>}Distinct total EMPLOYEE)
-Count({<[Año]={$(vAño)},HireTerm={'T'},MesN={"<6"}>}Distinct total EMPLOYEE),
if(RowNo()=7,

...

I want to make this smaller, i think that rowno() could be useful here, i would like to use it in my set analisys in this way

(If(RowNo()=1, $(vInicioPlantillaCNDistrital),
if(RowNo()=2,
$(vInicioPlantillaCNDistrital)+
Count({<[Año]={$(vAño)},HireTerm={'H'},MesN={"<RowNo()"}>}Distinct total EMPLOYEE)
-Count({<[Año]={$(vAño)},HireTerm={'T'},MesN={"<RowNo()"}>}Distinct total EMPLOYEE),
if(RowNo()=3,
$(vInicioPlantillaCNDistrital)+
Count({<[Año]={$(vAño)},HireTerm={'H'},MesN={"<RowNo()"}>}Distinct total EMPLOYEE)
-Count({<[Año]={$(vAño)},HireTerm={'T'},MesN={"<RowNo()"}>}Distinct total EMPLOYEE),
if(RowNo()=4,
$(vInicioPlantillaCNDistrital)+
Count({<[Año]={$(vAño)},HireTerm={'H'},MesN={"<RowNo()"}>}Distinct total EMPLOYEE)

...

It looks like this is working, but in my bar chart, for example, on february i have 9.07 (expected result) and when i use rowno in my set analysis it goes to 9.17, this is wrong, the difference is not a lot but i need to be precise about this value since i am calculating turnover.

 

Thanks !

1 Solution

Accepted Solutions

Re: RowNo Set analysis

Set analysis cannot be evaluated on row by row basis... is it evaluated once per chart and that is why <RowNo() in set analysis won't really work.... what you can do is to use Pick() function instead of If to make it a little faster and a little better looking

 

Pick(RowNo()=1,
$(vInicioPlantillaCNDistrital), $(vInicioPlantillaCNDistrital) + Count({<[Año]={$(vAño)},HireTerm={'H'},MesN={"<2"}>}Distinct total EMPLOYEE) -Count({<[Año]={$(vAño)},HireTerm={'T'},MesN={"<2"}>}Distinct total EMPLOYEE), $(vInicioPlantillaCNDistrital)+ Count({<[Año]={$(vAño)},HireTerm={'H'},MesN={"<3"}>}Distinct total EMPLOYEE) -Count({<[Año]={$(vAño)},HireTerm={'T'},MesN={"<3"}>}Distinct total EMPLOYEE), $(vInicioPlantillaCNDistrital)+ Count({<[Año]={$(vAño)},HireTerm={'H'},MesN={"<4"}>}Distinct total EMPLOYEE) -Count({<[Año]={$(vAño)},HireTerm={'T'},MesN={"<4"}>}Distinct total EMPLOYEE), $(vInicioPlantillaCNDistrital)+ Count({<[Año]={$(vAño)},HireTerm={'H'},MesN={"<5"}>}Distinct total EMPLOYEE) -Count({<[Año]={$(vAño)},HireTerm={'T'},MesN={"<5"}>}Distinct total EMPLOYEE), $(vInicioPlantillaCNDistrital)+ Count({<[Año]={$(vAño)},HireTerm={'H'},MesN={"<6"}>}Distinct total EMPLOYEE) -Count({<[Año]={$(vAño)},HireTerm={'T'},MesN={"<6"}>}Distinct total EMPLOYEE), ... )
2 Replies

Re: RowNo Set analysis

Set analysis cannot be evaluated on row by row basis... is it evaluated once per chart and that is why <RowNo() in set analysis won't really work.... what you can do is to use Pick() function instead of If to make it a little faster and a little better looking

 

Pick(RowNo()=1,
$(vInicioPlantillaCNDistrital), $(vInicioPlantillaCNDistrital) + Count({<[Año]={$(vAño)},HireTerm={'H'},MesN={"<2"}>}Distinct total EMPLOYEE) -Count({<[Año]={$(vAño)},HireTerm={'T'},MesN={"<2"}>}Distinct total EMPLOYEE), $(vInicioPlantillaCNDistrital)+ Count({<[Año]={$(vAño)},HireTerm={'H'},MesN={"<3"}>}Distinct total EMPLOYEE) -Count({<[Año]={$(vAño)},HireTerm={'T'},MesN={"<3"}>}Distinct total EMPLOYEE), $(vInicioPlantillaCNDistrital)+ Count({<[Año]={$(vAño)},HireTerm={'H'},MesN={"<4"}>}Distinct total EMPLOYEE) -Count({<[Año]={$(vAño)},HireTerm={'T'},MesN={"<4"}>}Distinct total EMPLOYEE), $(vInicioPlantillaCNDistrital)+ Count({<[Año]={$(vAño)},HireTerm={'H'},MesN={"<5"}>}Distinct total EMPLOYEE) -Count({<[Año]={$(vAño)},HireTerm={'T'},MesN={"<5"}>}Distinct total EMPLOYEE), $(vInicioPlantillaCNDistrital)+ Count({<[Año]={$(vAño)},HireTerm={'H'},MesN={"<6"}>}Distinct total EMPLOYEE) -Count({<[Año]={$(vAño)},HireTerm={'T'},MesN={"<6"}>}Distinct total EMPLOYEE), ... )
vmoreno2605
New Contributor III

Re: RowNo Set analysis

Now i understand why it is not working like that, in this case using pick wasn't useful, however thanks to your commentary about rowno and how it does not work on set analysis i found another way to solve this, thanks !