Rangesum query

[Pulkit_Thukral]

Hi,

I am not able to write a correct expression for the below scenario :

i have a daily trend for average availability :

Here availability is calculated by the expression : sum(availability) / count(TotalServers)

Now when i want to show this week data in a text box , 

formula currently used:

=(sum( {$<Call_Date={">=$(=Date(WeekStart(today())))<=$(=Date(today()))"},Call_Month,Call_Year=>} Availability)

/

count( {$<Call_Date={">=$(=Date(WeekStart(today())))<=$(=Date(today()))"},Call_Month,Call_Year=>} Total_Servers)

Problem: it is first adding availability of whole week and then dividing by total server count of whole week

formula to use:

((sum( {$<Call_Date={'$(=Date(today()-1))'},Call_Month,Call_Year=>} Availability)

/

count( {$<Call_Date={'$(=Date(today()-1))'}Call_Month,Call_Year=>} Total_Servers)

)

+

(sum( {$<Call_Date={'$(=Date(today()-2))'},Call_Month,Call_Year=>} Availability)

/

count( {$<Call_Date={'$(=Date(today()-2))'}Call_Month,Call_Year=>} Total_Servers)

)

+

.

.

.

+

(sum( {$<Call_Date={'$(=Date(today()-7))'},Call_Month,Call_Year=>} Availability)

/

count( {$<Call_Date={'$(=Date(today()-7))'}Call_Month,Call_Year=>} Total_Servers)

))

/ 7

Requirement:   i want to to calculate individually daily for this week then divide by weekday count.

                    How can this be achieved in a shorter valid expression as the above expression is very big.

[sunny_talwar]

What is the final number you are looking to get in the text box object?

99.037?

[Clever_Anjos · Accepted solution]

Maybe (hard to help without a sample of your data

=sum( AGGR(

sum( {$<Call_Date={">=$(=Date(WeekStart(today())))<=$(=Date(today()))"},Call_Month,Call_Year=>} Availability)

/

count( {$<Call_Date={">=$(=Date(WeekStart(today())))<=$(=Date(today()))"},Call_Month,Call_Year=>} Total_Servers)

),

Call_Date)) / 7

[sunny_talwar]

Clever what number are you aiming for in the text box object?

[Clever_Anjos]

I don´t know the number, just guessing what pulkit wants

[Pulkit_Thukral]

Hi Sunny.

Thanks for your reply.

I am looking for the number -98.9477.

The above number will come by adding individual availability from above chart and then dividing by 5.

Please be informed that i have got the above figure.

[Pulkit_Thukral]

Thanks alot .It worked .

[sunny_talwar]

Can you try this:

Avg({$<Call_Date={"$(='>=' & Date(Today() - 5, 'DateFieldFormatHere') & '<=' & Date(Today(), 'DateFieldFormatHere')"}, Call_Month, Call_Year>} Aggr(Sum({<Call_Month, Call_Year>} availability) / Count({<Call_Month, Call_Year>} TotalServers), Call_Date))

or

Sum({$<Call_Date={"$(='>=' & Date(Today() - 5, 'DateFieldFormatHere') & '<=' & Date(Today(), 'DateFieldFormatHere')"}, Call_Month, Call_Year>} Aggr(Sum({<Call_Month, Call_Year>} availability) / Count({<Call_Month, Call_Year>} TotalServers), Call_Date))/5

Not sure if you need 5 or 7 here, I think you need 5, but you seem to be going back 7. You will know better than me

[sunny_talwar]

Sorry, I did not know you already got the answer