6 Replies Latest reply: Aug 8, 2018 8:33 AM by daisy ch

# how to calculate the avg sum of amount?

Hi

In the below expression I am calculate the sum of amount for rolling 12 months.

sum({<[Event Creation Date]={">=\$(=MonthStart(AddMonths(Max([Event Creation Date])),-11),'MMM-YYYY')<=\$(=MonthEnd(Max([Event Creation Date]))),'MMM-YYYY')"}>}Amount)

Now I need the calculate average  sum of amount for rolling 12 months. How to write the expression.

Thanks

• ###### Re: how to calculate the avg sum of amount?

If you are showing data for 12 months, the average should be the amount divided by 12

= (sum({<[Event Creation Date]={">=\$(=MonthStart(AddMonths(Max([Event Creation Date])),-11),'MMM-YYYY')<=\$(=MonthEnd(Max([Event Creation Date]))),'MMM-YYYY')"}>}Amount))/12

• ###### Re: how to calculate the avg sum of amount?

Hi Sibin,

Actually  I am writing the expression for rolling 12 months. Present I have only 4 months data. If I use by 12 it is come exact avg value?.

• ###### Re: how to calculate the avg sum of amount?

In that case you can create a variable for Month count

variable name:

vMonthCount

Definition:

Count(Distinct Monthcolumn))

Then you expression should be

= (sum({<[Event Creation Date]={">=\$(=MonthStart(AddMonths(Max([Event Creation Date])),-11),'MMM-YYYY')<=\$(=MonthEnd(Max([Event Creation Date]))),'MMM-YYYY')"}>}Amount))/\$(vMonthCount)

• ###### Re: how to calculate the avg sum of amount?

Thanks Sibin.

I want do in back end level(script)

target date changed count the ID's.

I have table fields

table:

Issu_ID

Taget_date

-> KPI chart if the user selects any two dates compare those two dates.if maximum date and maximum previous dates in the Taget_date field are different then caluclate the count id's.

Example:

01                01-01-2018           10-05-2018

01                03-01-2018           15-05-2018

02                01-01-2018           10-05-2018

02                03-01-2018           10-05-2018

03                01-01-2018           10-05-2018

03                03-01-2018           16-05-2018

In the above table if we select dates in the date upload field i.e., 01-01-2018 and 03-01-2018 then count is 2 because id 01,03 target dates are changed and id 02 target dates are not changed.

so just count the Issues_ID when user select any two dates(ex:01-01-2018 is min date and 03-01-2018 is max date)compare those two dates if min date targetdate is onedate and max date targetdate is change to another date than caliculate the Issues_ID count.

I need quires for the above requirements in the back-end level.and write the expression front end level also if any simple way.

I have written quires in the front end level but the performance is slow and it is showing an error in the chart i.e., time out.

Thanking you.

• ###### Re: how to calculate the avg sum of amount?

How about if you do this

Avg(Aggr(Sum({<[Event Creation Date] = {">=\$(=MonthStart(AddMonths(Max([Event Creation Date])),-11), 'MMM-YYYY')<=\$(=MonthEnd(Max([Event Creation Date]))),'MMM-YYYY')"}>} Amount), MonthField))

or

Sum({<[Event Creation Date] = {">=\$(=MonthStart(AddMonths(Max([Event Creation Date])),-11), 'MMM-YYYY')<=\$(=MonthEnd(Max([Event Creation Date]))),'MMM-YYYY')"}>} Amount)/Count(DISTINCT {<[Event Creation Date] = {">=\$(=MonthStart(AddMonths(Max([Event Creation Date])),-11), 'MMM-YYYY')<=\$(=MonthEnd(Max([Event Creation Date]))),'MMM-YYYY')"}>} MonthField)

• ###### Re: how to calculate the avg sum of amount?

Hi Sunny,

I want do in back end level(script)

target date changed count the ID's.

I have table fields

table:

Issu_ID

Taget_date

-> KPI chart if the user selects any two dates compare those two dates.if maximum date and maximum previous dates in the Taget_date field are different then caluclate the count id's.

Example:

01                01-01-2018           10-05-2018

01                03-01-2018           15-05-2018

02                01-01-2018           10-05-2018

02                03-01-2018           10-05-2018

03                01-01-2018           10-05-2018

03                03-01-2018           16-05-2018

In the above table if we select dates in the date upload field i.e., 01-01-2018 and 03-01-2018 then count is 2 because id 01,03 target dates are changed and id 02 target dates are not changed.

so just count the Issues_ID when user select any two dates(ex:01-01-2018 is min date and 03-01-2018 is max date)compare those two dates if min date targetdate is onedate and max date targetdate is change to another date than caliculate the Issues_ID count.not in between dates.

I need quires for the above requirements in the back-end level.and write the expression front end level also if any simple way.

I have written quires in the front end level but the performance is slow and it is showing an error in the chart i.e., time out.

Thanking you.