Calculate hours

[Sunflower_00]

Hi 

I am trying to show new consultants that have worked 50hrs for  the first 30 days. Their start dates and hours are different and jump into the following month. 

I tried this calculation but it returns nulls. What am I missing or doing wrong?

if(max(aggr(count(distinct {<Hours, Date={"<=$(=Date(AddMonths(Max(Date), -1) + 30))"}>} ID),group={' service'}))
- min(aggr(count(distinct {<Hours , Date={"<=$(=Date(AddMonths(Max(Date), -1) + 30))"}>} ID), group={'service'}))>=50, 1, 0)

[marcus_sommer]

I think there are various issues within your approach. At first there is no dimension specified for the aggr() only a set analysis which should be placed before the inner aggregation and not after it. Further the condition within the count() queried the max-date from previous months and adds 30 days which seems not be logically in regard to your description - also that it counts an ID and not summed the hours and this is then wrapped by a max/min ... ?

Beside this if you want to calculate hours for consultants with various overlapping period-information you couldn't do it within a set analysis because a set analysis evaluates against columns but not rows. If you really need a row-level evaluation you need to apply it within an if-loop.

Personally I would tend to a quite different approach and calculating an offset-value between the first date from each consultant and row-date and using this then as dimension/selection/condition. Meant is something like:

m: mapping load Consultant, date(min(Date))
       resident Facts group by Consultant;

Final:
load *, class(Offset, 30) as OffsetCluster;
load *, Date - applymap('m', Consultant) + 1 as Offset
resident Facts;

and then using OffsetCluster to query sum/min/max/avg of the Hours and within another chart maybe a calculated dimension with:

class(aggr(sum(Hours), OffsetCluster), 10)

and as expression something like: count(Consultant).

[Sunflower_00]

Thank you for your response. 

I want to do this in the front end.

[marcus_sommer]

IMO you shouldn't attempt it within the UI because all essential logic should be done within the data-model. It's more simple and flexible, re-usable and much more performant as in the UI - especially if the logic requires nested if-loops and/or aggr-constructs.

Nevertheless you may try something like:

sum(aggr(if(sum(aggr(if(Datum <= max(Datum) - 30, sum(Hour)), Consultant))>= 50, 1, 0), Dim1, Dim2))

whereby Dim1 and Dim2 are just placeholder for the dimensional context in which the count should happens. Just do it step by step with each single calculation-part as separate expression within a table-chart to see if it returned the expected results and if you could start to combine them and checking them, too.