I want to be able to use sumOver to calculate days of employee productivities less than 60% of their crew’s productivities. Since my calculation needs to sumover the date, then date has to be in the fields. When collapsing the date field, then this calculation changes to zero. Below is a screenshot of the table’s functionality. The first three employee IDs are not collapsed to show that the calculation works if date is not collapsed. The later employee IDs show that the calculation nulls out when collapsing the date field.
limitations: the dataset has multiple line items per day, with “primera” counts1 needing to be summed, but divided by all specific_task hours. Thus there may be a need for POST_AGG_FILTER within a potential PRE_AGG solution.
Level01 is the column that identifies crew.
Are you trying to calculate the productivity at the day level or employee level? Also, can you please provide the calculation you are using for Counts1. If you are calculating it at the employee level, do you need the {Work_Date} in the partition clause?
Hi @salim ,
Thank you for your response. I need to calculate the productivity at the day, employee, and crew level for part of the calculation. Counts1 is a column in the dataset, so it is not a calculated field, it is the number of harvested boxes for the line item.
The desire is to calculate the sum of days when an employee-date-crew productivity is 60% or below a date-crew productivity, thus I am under the impression that I need {Work_Date} in the partition clause for each step of the calculation.
Were you able to create the calculation required for your use-case ?
If not. Can you please share sample data, we can work on this calc and share the steps
We have not heard back from you regarding your question. We would still like to help. If we do not hear back in the next 7 days, we will archive the question. Thanks.
I am not familiar with how to provide a sample dataset. I would prefer to receive help with pseudocode if possible
I am also considering a direct query solution where I group four separate tables and join them to avoid the LAC calculations. This is not the preferred solution, but I feel like there are limitations in the LAC route when it comes to collapsing a partition field. Is this correct?
Yes there are limitations for LAC calculations for partition fields. I would suggest doing it in SQL because you can use those fields without depending on the partition field in very visual.
