Problem with expression

[#1]

Good evening,

I am experiencing a slight problem with expressions,I am trying to calculate the average of this expression using set analysis and the dimension = Day

=if(((Sum (TimeBetweenLeadsFinal)-(0.0625*count(distinct Sale_DateM)))/(count(distinct Sale_DateM)*0.270833333))<0,

  ((Sum (TimeBetweenLeadsFinal)))/(count(distinct Sale_DateM)*0.270833333),

  ((Sum (TimeBetweenLeadsFinal)-(0.0625*count(distinct Sale_DateM)))/(count(distinct Sale_DateM)*0.270833333)))

Thanks

[sunny_talwar]

May be this:

Avg(Aggr(

if(((Sum (TimeBetweenLeadsFinal)-(0.0625*count(distinct Sale_DateM)))/(count(distinct Sale_DateM)*0.270833333))<0,

  ((Sum (TimeBetweenLeadsFinal)))/(count(distinct Sale_DateM)*0.270833333),

  ((Sum (TimeBetweenLeadsFinal)-(0.0625*count(distinct Sale_DateM)))/(count(distinct Sale_DateM)*0.270833333)))

, Day))

[#3]

Actually the expression you gave is correct but I forgot to mention that I need to calculate Month To Date(MTD), the expression I am looking for is probably like this below:

Avg(Aggr(if(sum({<Month=, Quarter=, Week=, Day, DateNum={">=$(=Num(MonthStart(Max(DateNum))))<=$(=Max(DateNum))"}>}TimeBetweenLeadsFinal)- (0.0625*count(distinct Sale_DateM))/(count(distinct Sale_DateM)*0.270833333))<0,
	sum({<Month=, Quarter=, Week=, Day, DateNum={">=$(=Num(MonthStart(Max(DateNum))))<=$(=Max(DateNum))"}>}TimeBetweenLeadsFinal)/(count(distinct Sale_DateM)*0.270833333),
	sum({<Month=, Quarter=, Week=, Day, DateNum={">=$(=Num(MonthStart(Max(DateNum))))<=$(=Max(DateNum))"}>}TimeBetweenLeadsFinal)-(0.0625*count(distinct Sale_DateM))/(count(distinct Sale_DateM)*0.270833333))),Day))

[sunny_talwar]

So is this not working then?

Avg({<Month, Quarter, Week, Day, DayNum = {">=$(=Num(MonthStart(Max(DateNum))))<=$(=Max(DateNum))"}>} Aggr(If(Sum({<Month, Quarter, Week, Day, DateNum = {">=$(=Num(MonthStart(Max(DateNum))))<=$(=Max(DateNum))"}>} TimeBetweenLeadsFinal) - (0.0625 * Count(DISTINCT Sale_DateM)) / (Count(DISTINCT Sale_DateM) * 0.270833333)) < 0,

Sum({<Month, Quarter, Week, Day, DateNum = {">=$(=Num(MonthStart(Max(DateNum))))<=$(=Max(DateNum))"}>} TimeBetweenLeadsFinal) / (Count(DISTINCT Sale_DateM) * 0.270833333),

Sum({<Month, Quarter, Week, Day, DateNum = {">=$(=Num(MonthStart(Max(DateNum))))<=$(=Max(DateNum))"}>} TimeBetweenLeadsFinal) - (0.0625 * Count(DISTINCT Sale_DateM)) / (Count(DISTINCT Sale_DateM) * 0.270833333))), Day))

[#5]

So what I am trying to achieve is something like the graph below, where I need to calculate Averages of the following expressions:

Sum ({<PreviewedFirst={1}>}PreviewTime)/sum(timespent) ( to Calculate Average of % Preview)

Sum(CallTime)/sum(timespent)  ( to Calculate Average of %CallTime)

Sum(WraptimeLastCall)/sum(timespent) ( to Calculate Average of %WrapTime)

Sum (TimeBetweenLeadsFinal)-(0.0625*count(distinct(LTheDate)))/sum(timespent)  (to Calculate Average of %TimeBetweenLeads)

Sum(timespent)/(count(distinct Sale_DateM)*0.270833333) (to calculate Average of % utilisation)

and I use  Month=, Quarter=, Week=, Day, DateNum={">=$(=Num(MonthStart(Max(DateNum))))<=$(=Max(DateNum))"} to calculate MTD.

[sunny_talwar]

What you want is always good to see, but it becomes very difficult to suggest anything in a complicated expression such as yours without a sample. Would you be able to share a sample.

Best,

Sunny