Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

stuhico09
Contributor

How to display the average total count for these 12 months?

My detail as below:

Dimension:

=[Date Created.autoCalendar.YearMonth]

Measure:

=Count({$ <Temp={'Task_17'}>*$<[Date Created.autoCalendar.YearMonth]={">=$(=AddMonths(MonthStart(Today()-20),-11))"}> } [SR No])

Subtitle:

=Count({$ <Temp={'Task_17'}>*$<[Date Created.autoCalendar.YearMonth]={">=$(=AddMonths(MonthStart(Today()-20),-11))"}> } [SR No])

*My subtitle show 3483. But what i want is 496/12=41.33. Please advise.

qi.PNG

3 Replies

Re: How to display the average total count for these 12 months?

Try this.

Sum(Aggr(Count({$ <Temp={'Task_17'}>*$<[Date Created.autoCalendar.YearMonth]={">=$(=AddMonths(MonthStart(Today()-20),-11))"}> } [SR No]),[Date Created.autoCalendar.YearMonth]))/12


Regards,

Kaushik Solanki

stuhico09
Contributor

Re: How to display the average total count for these 12 months?

Hi Kaushik,

By using your formula i get the average as 42.25. i think it include the null value.

i try to exclude them using this formula but it is not working.

My formula:

Sum(Aggr(Count({$ <Temp={'Task_17'}>*$<[SR No]-={"''"}>

*$<[Date Created.autoCalendar.YearMonth]={">=$(=AddMonths(MonthStart(Today()-20),-11))"}> }

[SR No]),[Date Created.autoCalendar.YearMonth]))/12


qi2.PNG

Please help. Thanks.

Re: How to display the average total count for these 12 months?

Hi,

Can you share the sample data.

Regards,

Kaushik Solanki