// // KPI_TABLE: // // LOAD*, // // if(ID = Previous(ID) and ANNEE = Previous(YEAR)+1 // // and MOIS = Previous(MONTH) and VALUE >0 and Previous(VALUE)>0, 1, 0) as PermanentStaff // // Resident [DATA_KPI]; // // Drop Table[DATA_KPI];
But it doesn't work.
I'm trying to check the record from current year and previous year looking at the ID. If the ID exist in previous period and current period and if there is data in the previous period and the current period and the month from last year and the month fro this year then it's pemanent staff.
But i always get 0 from permanent staff which i don't understand.
Is there any reason the script has comment tags on it (//)?
A (single) Previous statement looks at the row before in your load, so if there are monthly records for each ID I am not sure you are going to be able to use this to assign PermanentStaff if your criteria cover a number of rows between months and years, but I am not sure I understand your requirement.
Are you able share some (anonymised) data and how you would tag it up?