topic Re: Problem with expression in QlikView

Problem with expression

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Problem with expression

sunny_talwar — Mon, 05 Dec 2016 20:10:34 GMT

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))

Re: Problem with expression

Mon, 05 Dec 2016 20:34:43 GMT

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.0625count(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.0625count(distinct Sale_DateM))/(count(distinct Sale_DateM)0.270833333))),Day))

Re: Problem with expression

sunny_talwar — Mon, 05 Dec 2016 20:38:33 GMT

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))

Re: Problem with expression

Mon, 05 Dec 2016 20:59:52 GMT

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.

Re: Problem with expression

sunny_talwar — Mon, 05 Dec 2016 21:02:22 GMT

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