Skip to main content
Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
Pulkit_Thukral
Partner - Creator II
Partner - Creator II

Rangesum query

Hi,

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

i have a daily trend for average availability :

1.PNG

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.



1 Solution

Accepted Solutions
Clever_Anjos
Employee
Employee

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

View solution in original post

8 Replies
sunny_talwar

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

99.037?

Clever_Anjos
Employee
Employee

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

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

Pulkit_Thukral
Partner - Creator II
Partner - Creator II
Author

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
Partner - Creator II
Partner - Creator II
Author

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