topic Re: Rangesum query in QlikView

Rangesum query

Pulkit_Thukral — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Rangesum query

sunny_talwar — Sat, 21 May 2016 13:27:46 GMT

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

99.037?

Re: Rangesum query

Clever_Anjos — Sat, 21 May 2016 13:44:18 GMT

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

Re: Rangesum query

sunny_talwar — Sat, 21 May 2016 14:02:15 GMT

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

Re: Rangesum query

Clever_Anjos — Sat, 21 May 2016 14:05:13 GMT

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

Re: Rangesum query

Pulkit_Thukral — Sat, 21 May 2016 18:26:24 GMT

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.

Re: Rangesum query

Pulkit_Thukral — Sat, 21 May 2016 18:29:37 GMT

Thanks alot .It worked .

Re: Rangesum query

sunny_talwar — Sat, 21 May 2016 18:34:31 GMT

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

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

Re: Rangesum query

sunny_talwar — Sat, 21 May 2016 18:35:00 GMT

Sorry, I did not know you already got the answer