how to calculate the avg sum of amount?

[daisy1438]

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

[sibin_jacob]

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

[daisy1438]

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?.

[sibin_jacob]

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)

[daisy1438]

Thanks Sibin.

please help on below requirement if you get any idea.

I want do in back end level(script)

target date changed count the ID's.

I have table fields

table:

Issu_ID

date_upload

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:

Issue_ID      Date_upload      Target_date

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.

[sunny_talwar]

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)

[daisy1438]

Hi Sunny,

Please help on below requirement if you get any idea.

I want do in back end level(script)

target date changed count the ID's.

I have table fields

table:

Issu_ID

date_upload

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:

Issue_ID      Date_upload      Target_date

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.