topic Ignoring current month in a calculation in QlikView

Ignoring current month in a calculation

Wed, 02 Nov 2011 13:29:00 GMT

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

Ignoring current month in a calculation

Wed, 02 Nov 2011 13:59:04 GMT

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.

Ignoring current month in a calculation

swuehl — Wed, 02 Nov 2011 14:09:22 GMT

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

Ignoring current month in a calculation

Wed, 02 Nov 2011 14:25:20 GMT

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.