Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
ugurarslan
Contributor

Average of multiple sum % calculations different months

Dear all,

I need to take the average of sums of different months:

Sum 1:

(Sum({<[Year Month]={'$(Current_Month_YYYYMM)'}>}[Cash Amt USD])

/

Sum({<[Year Month]={'$(Current_Month_YYYYMM)'},[Type Code]={'INV'}>}total <[Year Month]>[Amt USD]))

Sum 2:

(Sum({<[Year Month]={'$(Prior_Month_YYYYMM)'}>}[Cash Amt USD])

/

Sum({<[Year Month]={'$(Prior_Month_YYYYMM)'},[Type Code]={'INV'}>}total <[Year Month]>[Amt USD]))


Sum 3:

(Sum({<[Year Month]={'$(Prior_Quarter_YYYYMM)'}>}[Cash Amt USD])

/

Sum({<[Year Month]={'$(Prior_Quarter_YYYYMM)'},[Type Code]={'INV'}>}total <[Year Month]>[Amt USD]))


Sum 4:

(Sum({<[Year Month]={'$(Prior_Year_YYYYMM)'}>}[Cash Amt USD])

/

Sum({<[Year Month]={'$(Prior_Year_YYYYMM)'},[Type Code]={'INV'}>}total <[Year Month]>[Amt USD]))


I tried:

Avg(

(Sum({<[Year Month]={'$(Current_Month_YYYYMM)'}>}[Cash Amt USD])

/

Sum({<[Year Month]={'$(Current_Month_YYYYMM)'},[Type Code]={'INV'}>}total <[Year Month]>[Amt USD]))

+

(Sum({<[Year Month]={'$(Prior_Month_YYYYMM)'}>}[Cash Amt USD])

/

Sum({<[Year Month]={'$(Prior_Month_YYYYMM)'},[Type Code]={'INV'}>}total <[Year Month]>[Amt USD]))

+

(Sum({<[Year Month]={'$(Prior_Quarter_YYYYMM)'}>}[Cash Amt USD])

/

Sum({<[Year Month]={'$(Prior_Quarter_YYYYMM)'},[Type Code]={'INV'}>}total <[Year Month]>[Amt USD]))

+

(Sum({<[Year Month]={'$(Prior_Year_YYYYMM)'}>}[Cash Amt USD])

/

Sum({<[Year Month]={'$(Prior_Year_YYYYMM)'},[Type Code]={'INV'}>}total <[Year Month]>[Amt USD])))


And this resulted in an error.


Please help me.


Ugur

Tags (2)
4 Replies
Luminary
Luminary

Re: Average of multiple sum % calculations different months

Nested aggregation is not allowed.

You can just divide the result of all sums by 4, or I am missing something?

ugurarslan
Contributor

Re: Average of multiple sum % calculations different months

that doesn't give me result which I would like to see also.

ugurarslan
Contributor

Re: Average of multiple sum % calculations different months

it gives me a '-'

Luminary
Luminary

Re: Average of multiple sum % calculations different months

What kind of average do you want to see? Maybe check this: Average – Which average?