how to calculate the avg sum of amount? - Qlik Community

daisy1438 · ‎2018-08-08

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 · ‎2018-08-08

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 · ‎2018-08-08

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 · ‎2018-08-08

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 · ‎2018-08-08

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 · ‎2018-08-08

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 · ‎2018-08-08

Hi Sunny,

Please help on below requirement if you get any idea.