RowNo Set analysis

[vmoreno2605]

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 !

[sunny_talwar · Accepted solution]

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]

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 !