Script error: Invalid expression

[Narz]

I have employee data - EmpName, Joining Date, Exit Data and added Flag value status as current, New and exit. Each month there are new and exit employees. The next month current is a calculated value using previous month data.

I am getting Invalid Expression for the below script. Can someone please help me understand what is missing in the below.

Load
count(if(status='Current',"EmpName")) as JanExisting,

count(if(status='Current',"EmpName"))
+count(if(status='New' and Month='Jan',"EmpName")) -
count(if(status='Exit' and Month='Jan',"EmpName"))
as JanFinal
Resident MyTable;

Load
JanFinal +
count(if(status='New' and Month='Feb', "EmpName", null())) -
count(if(status='Exit' and Month='Feb', "EmpName", null()))
as FebFinal

Resident MyTable;

Load
FebFinal +
count(if(status='New' and Month='Mar', "EmpName", null())) -
count(if(status='Exit' and Month='Mar', "EmpName", null()))
as MarFinal
Resident MyTable;

[Clement15]

Hello, to use aggregation functions, here the count, you need to use a group by

[Narz]

My first block calculation executes fine and I am able to get JanFinal without Group by. 

When I try to add Feb data I am getting error. May I know is it only Feb block count fails and Jan block count works without Group by?

[Clement15]

Do you have some sample data so I can try your code?

[BrunPierre]

The initial block runs smoothly as it exclusively uses the count aggregation function. Yet, examining the second and third blocks, namely JanFinal and FebFinal respectively, they lack aggregation or a GROUP BY clause within the same statement.

For instance, it should be either "Load Sum(JanFinal) + ..." or "Group By JanFinal".

[marcus_sommer]

I think there are better ways to get the wanted information as combining n aggregation-loads to produce an accumulation.

For the most kind of views you won't need to access the dimension-table else using:

count(distinct EmpName)

within the UI would return the number of employees in regard to the used dimensions and selections. And with some set analysis and/or TOTAL statements the behaviour could be adjusted which means you could have flexible views and not just a fixed one.

[Narz]

Kindly see attached sample Data.

• Sheets - Current, New, Exit are my input data.
• Expected output is my table in the Dashboard. 
• Current, New and Exit are field values based on the input data.
• Final count is calculated field Current +New - Exit (in the current data it is for Jan Month).
• The Final count for Jan has to be input as Current for Feb Month.
•  With the Feb Current (Jan Final Output) calculation will follow as below.
• Feb Current + Feb New - Feb Exit = Feb Final count.
• Feb Final Count will be March Current Value. 
• New and Exit data will be given as input every month. 
• The current has to be previous month Calculated value.

Please help me a way to do the calculation for all months. When I calculate in script the calculated value is assigned for Jan and I am not able to input the value for Feb.