Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in NYC Sept 4th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Ignoring current month in a calculation

I have the formula beloe which calculates the mean of a data set

(Count(total distinct{$<PROBLEM_CODE={'CM'}>}(REQUEST_ID))/count(total YearMonth))*12

As the current month is not yet complete I don't want to include it in in my calculation. Does anybody know how to do this?

My Year Month field is in the YY-MM format i.e 11-10 equals 2011 October

3 Replies
Not applicable
Author

Hi Haycockd,

I am not sure about the logic but there is some systax error that I can correct. Try the below expression:

( Count(total distinct{<PROBLEM_CODE={'CM'}>} REQUEST_ID)/count(total <YearMonth> REQUEST_ID) )*12

PS: Please be vigilant as distinct is in the numerator but missing in the denomiator.

Hope this solves your problem.


Kiran.

swuehl
MVP
MVP

Maybe something like

(Count(total distinct{$<PROBLEM_CODE={'CM'}, YearMonth -= {$(maxstring(YearMonth))} >} REQUEST_ID )/ (count(total distinct YearMonth)-1))*12

I assumed you want to divide by the number of months to get an average per month, not a total share (I guess that what's Kiran assuming).

Hope this helps,

Stefan

Not applicable
Author

In this instrance I don't want average by month becuase I want to calcuate the mean. My formula does this a treat but ideally I don't want the current month as it is not yet over. All request Ids are not raised until the end of the month which is why I am trying to ignore this month.

Currently users have to select every month but this the current one which is not ideal.