Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
natalia_larrea
Contributor II
Contributor II

Count last 12 month KPI

Hello Everybody!

I need help with this KPI.

I have field [date_submitted.autoCalendar.Month] 

And I need to count Quantity of opportunities Opened the last 12 month. 

natalia_larrea_0-1657734628003.png

Total last 12 month= 1680 opportunities 

then I need to calculate the average:

1680/12 = Avg 140 opportunities

 

something Like that??

=count( { < date_submited = {"Month(AddMonths(Today(),-12)) "} > } opportunities )   /12

Thaks for your Hep!

 

Labels (3)
8 Replies
ggijben
Partner - Creator II
Partner - Creator II

Hi @natalia_larrea , 

Can you try:

count( { < date_submited = {">=$(=Monthstart(AddMonths(Today(),-12))) <=  $(=Monthend(AddMonths(Today(),-1)))"} > } opportunities )    /    12
natalia_larrea
Contributor II
Contributor II
Author

thanks @ggijben . 

this does not works. I keep trying

ggijben
Partner - Creator II
Partner - Creator II

Can you provide the value of the Dimension and Measure used in your bar chart?

natalia_larrea
Contributor II
Contributor II
Author

This is  the Dimension 

natalia_larrea_1-1657736759364.png

and the Measure 

natalia_larrea_2-1657736789036.png

 

ggijben
Partner - Creator II
Partner - Creator II

Can you try

count(distinct { < date_submited = {">=$(=Monthstart(AddMonths(Today(),-12))) <=  $(=Monthend(AddMonths(Today(),-1)))"} > } OpprId )    /    12

Or else:

SUM( AGGR( count(distinct { < date_submited = {">=$(=Monthstart(AddMonths(Today(),-12))) <=  $(=Monthend(AddMonths(Today(),-1)))"} > } OpprId ), [date_submitted.autoCalendar.YearMonth]))    /    12



vinieme12
Champion III
Champion III

You need to compare date_submitted with date values and not Month values

 

 

=count( { < date_submited = {">=$(=Monthstart(Max([date_submitted.autoCalendar.DATEFIELD] ),-12))"} > } opportunities )  

 

replace DATEFIELD with actual datefield name

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
natalia_larrea
Contributor II
Contributor II
Author

I tried but it doesn't work and I don't know why. From what I searched on the internet it seems to be the solution... 😪

vinieme12
Champion III
Champion III

can you post the exact expression you used?

 

and a screenshot of the entire expression editor

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.