1

I need to totalize annual employee holidays, with this rules:

  1. For any day show the number of holidays availables for a employee
  2. Sum holidays of different employees
  3. Sum holiday for different years and same employees

For example:

employeeId Date Holidays
1 2020-12-30 30
1 2020-12-31 30
1 2021-01-01 30
2 2020-12-30 30
2 2020-12-31 30
2 2021-01-01 30
  • Total holidays of employee 1 in 2020: 30
  • Total holidays of employee 1: 60
  • Total holidays: 120

How should I create the custom aggregator to solve the problem?

Thanks

josellm
  • 23
  • 7
  • How do you calculate this measure in SQL? or Excel? – Vitaliy Fedorchenko Mar 05 '21 at 18:42
  • Is a good question, in SQL to calculate total employee for employee, I need last date of the period for employee and sum the holidays for different employees: select e.id, sum(e.holidays) from Employees e inner join ( select Id, year(date) as year,max(date) as last from Employees group by id, year(date)) d on e.id = d.id and e.date=d.last group by e.id – josellm Mar 05 '21 at 22:42
  • You may use the same approach with PivotData: pre-aggregate your data by year (for "Holidays" I guess "Max" will get a correct number) to get a dataset like "employeeId", "year", "Holidays", and then use it as an input to get summary data with "Sum" (by "Holidays") aggregator. – Vitaliy Fedorchenko Mar 06 '21 at 12:28

1 Answers1

1

Finally, I created a custom aggregator. I pass a context object in the constructor, in this object I have the position of the field "employeeId"(the order of the fields can change) and the current index "dimension". I'm just creating a tree for example:

Sum(Holidays)->employeeId,Sum(Holidays)->employeeId,year,Sum(Holidays)->employeeId,year,date,Holidays

  public class AccumAggregatorFactory : IAggregatorFactory {
    private readonly CubeContext cc;

    public AccumAggregatorFactory(CubeContext cc) {
        this.cc = cc;
    }

    public IAggregator Create() => (IAggregator)new AccumAggregator(cc);

    public IAggregator Create(object state) => (IAggregator)new AccumAggregator(cc, state);

    public override bool Equals(object obj) => obj is AccumAggregatorFactory aggregatorFactory;

    public override string ToString() => "Accum of Holidays";
}

public class AccumAggregator : IAggregator {
    private object id;
    private Decimal total;
    private Decimal closuresValue;
    private uint count;
    private readonly CubeContext cc;

    
    public AccumAggregator(CubeContext cc) {
        this.cc = cc;
    }

    public object Value => total;

    public uint Count => count;

    public string Keys { get; internal set; }

    public void Merge(IAggregator aggr) {
        if (!(aggr is AccumAggregator accum))
            throw new ArgumentException(nameof(aggr));
        
        decimal curAccum = 0; //Add to accumulated value if is needed
        if (cc.DimIndexIdItem == cc.dim) {
            curAccum = closuresValue; //I need to accum closures
            if (IsClosure(accum, cc.DateEnd))
                closuresValue += accum.total;
        } else if (cc.dim < cc.DimIndexIdItem) {
            if (IsClosure(accum, cc.DateEnd)) {
                curAccum = closuresValue;
                closuresValue += accum.total;
            }
        }
        total = curAccum + accum.total;
        
        id = accum.id;
        date = accum.date;
    }

    private static bool IsClosure(AccumAggregator accum, DateTime dateEnd) {
        if (accum.id == null)
            return false;
        if (accum.date == DateTime.MinValue)
            return false;
        if (dateEnd == accum.date)
            return true;
        return accum.date.Month==12 && accum.date.Day==31;
    }

    public object GetState() => new object[2] { Count, Value };
}
josellm
  • 23
  • 7