Need help with average of sum of values in gauge chart

[#1]

I have a requirement where i need to show the average of sum of values in straight table as well as Gauge Chart but only thing is that if we select the month in calendar , it should sum the values of selected month as well as months less than selected month and show the average

i am able to get the same in straight table by using an expression but the values are not matching when i am trying to do the same in gauge chart by using Aggr() function.

Please refer the attached qvw and xls file where i have tried this but not able to do so in gauge chart . Please help

[sunny_talwar · Accepted solution]

Is this what you are looking for? PFA

Best,

Sunny

[#3]

Yes Thanks buddy

[sunny_talwar]

‌No problem

[#5]

this sample application is working fine but when i am trying to implement the same in my application its not working

when i am selecting a particular month , i am getting the count of  ITEMID added on or before that selected month

in a text box . Here is the expression which i am using in text box and my straight table

=count({$<Call__MonthSerial ={"<=$(=$(vMaxSelectedMonth))"},Call_Month=,[Call_Day of Month]=,Call_Year=}>} DISTINCT ITEMID)

but when i am trying to use the same as below in my gauge chart then its giving me average of aggregate of the ITEMID which are mapped to that selected month hence my values are not matching as straight table

Num(Avg({$<Call__MonthSerial ={"<=$(=$(vMaxSelectedMonth))"},Call_Month=,[Call_Day of Month]=,Call_Year=>} Aggr(Num(1- (Sum({$<[outage_type]={1}>} duration_sec))/Num#(Interval(MonthEnd([Call_Year-Month])-MonthStart([Call_Year-Month]),'ss'))  ,'##.#####'),ITEMID)),'##.####')

I have 4 ITEMS for April and 13 for MAY so the first expression is returning 17 as ITEMID as count but when i am using the same in gauge chart then its calculating aggregation for 13 but the same is considering 17 in straight table hence the average is varying

[sunny_talwar]

This part of your expression seem different than what you have in your straight table:

Aggr(Num(1- (Sum({$<[outage_type]={1}>} duration_sec))/Num#(Interval(MonthEnd([Call_Year-Month])-MonthStart([Call_Year-Month]),'ss'))  ,'##.#####')

Is there a reason you are not using this?

=Num(Avg({$<Call__MonthSerial ={"<=$(=$(vMaxSelectedMonth))"},Call_Month=,[Call_Day of Month]=,Call_Year=>} Aggr(Count({$<Call__MonthSerial ={"<=$(=$(vMaxSelectedMonth))"},Call_Month=,[Call_Day of Month]=,Call_Year=}>} DISTINCT ITEMID), ITEMID)),'##.####')

[#7]

well i am trying to get the duration of outage here . here duration_sec is dountime seccond which i am trying to divide by total interval and substracting by 1 to get the uptime of all the distinct items which are added before the selected month hence i am using below expression in gauge

Num(Avg({$<Call__MonthSerial ={"<=$(=$(vMaxSelectedMonth))"},Call_Month=,[Call_Day of Month]=,Call_Year=>} Aggr(Num(1- (Sum({$<[outage_type]={1}>} duration_sec))/Num#(Interval(MonthEnd([Call_Year-Month])-MonthStart([Call_Year-Month]),'ss'))  ,'##.#####'),ITEMID)),'##.####')

[#8]

sunindia‌

Any idea why i am not getting the total no of count of the items when i am applying in the gauge expression

[sunny_talwar]

It would be very hard to determine without looking at the application. Will it be possible to share your application?