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

    how to calculate the avg sum of amount?

    daisy ch

      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?
          Sibin Jacob.C C

          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?
              daisy ch

              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?
                  Sibin Jacob.C C

                  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?
                      daisy ch

                      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.

                • Re: how to calculate the avg sum of amount?
                  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)

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

                      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.