Countif Variable for Prior 6 Months

[Anonymous]

Hi,

I've created a variable which is basically one really long expression. The possible outcomes of this variable are 1,2 or 3. What I'd like to do is write an expression that counts the number of time the expression yields a 1 in the most recent 6 months. I imagine a countif is needed here.

Thanks in advance!

[petter]

Count( If( Aggr( $(vExpression) = 1 , 1 ) , <dim1> ) )

It is hard to give you an more exact answer since it is important to know the context of the entire expression. Is it going to be used in a chart/table with dimensions? If so the <dim1> has to be replaced by all the dimensions listed.

The Aggr() function is necessary since you have an aggregation in your expression most probably. The aggregation needs inside your expression needs to have a set expression which filters out the prior six months. If one of your dimensions in the chart/table that you embed this variable-with-expression in is a date then you will have to override the date selection that the dimension enforce. This can be done by using the TOTAL qualifier inside your expressions aggregation function...

[Anonymous]

Thanks for helping out. I'll provide a bit more context since I'm not quite getting it form your response. The expression I've turned into a variable is below (quite long): And I have a date field called "[Reporting Month]"

if(((((Sum([Active Clients Low Risk])/sum([Active Clients])*(1/10))
+
(Sum([Active Clients Medium Risk])/sum([Active Clients])*(2/10))
+
(Sum([Active Clients Moderate-High Risk])/sum([Active Clients])*(3/10))
+
(Sum([Active Clients High Risk])/sum([Active Clients])*(4/10)))
-(1/10))
/((4/10-(1/10)))*100)

/

(rangeavg(

((((Sum([Active Clients Low Risk])/sum([Active Clients])*(1/10))
+
(Sum([Active Clients Medium Risk])/sum([Active Clients])*(2/10))
+
(Sum([Active Clients Moderate-High Risk])/sum([Active Clients])*(3/10))
+
(Sum([Active Clients High Risk])/sum([Active Clients])*(4/10)))
-(1/10))
/((4/10-(1/10)))*100)

,0,12)

/10)>1.2
or
((((Sum([Active Clients Low Risk])/sum([Active Clients])*(1/10))
+
(Sum([Active Clients Medium Risk])/sum([Active Clients])*(2/10))
+
(Sum([Active Clients Moderate-High Risk])/sum([Active Clients])*(3/10))
+
(Sum([Active Clients High Risk])/sum([Active Clients])*(4/10)))
-(1/10))
/((4/10-(1/10)))*100)

/

(rangeavg(

((((Sum([Active Clients Low Risk])/sum([Active Clients])*(1/10))
+
(Sum([Active Clients Medium Risk])/sum([Active Clients])*(2/10))
+
(Sum([Active Clients Moderate-High Risk])/sum([Active Clients])*(3/10))
+
(Sum([Active Clients High Risk])/sum([Active Clients])*(4/10)))
-(1/10))
/((4/10-(1/10)))*100)

,0,12)

/10)<.8,
3,

if (((((Sum([Active Clients Low Risk])/sum([Active Clients])*(1/10))
+
(Sum([Active Clients Medium Risk])/sum([Active Clients])*(2/10))
+
(Sum([Active Clients Moderate-High Risk])/sum([Active Clients])*(3/10))
+
(Sum([Active Clients High Risk])/sum([Active Clients])*(4/10)))
-(1/10))
/((4/10-(1/10)))*100)

/

(rangeavg(

((((Sum([Active Clients Low Risk])/sum([Active Clients])*(1/10))
+
(Sum([Active Clients Medium Risk])/sum([Active Clients])*(2/10))
+
(Sum([Active Clients Moderate-High Risk])/sum([Active Clients])*(3/10))
+
(Sum([Active Clients High Risk])/sum([Active Clients])*(4/10)))
-(1/10))
/((4/10-(1/10)))*100)

,0,12)

/10)>.9
and
((((Sum([Active Clients Low Risk])/sum([Active Clients])*(1/10))
+
(Sum([Active Clients Medium Risk])/sum([Active Clients])*(2/10))
+
(Sum([Active Clients Moderate-High Risk])/sum([Active Clients])*(3/10))
+
(Sum([Active Clients High Risk])/sum([Active Clients])*(4/10)))
-(1/10))
/((4/10-(1/10)))*100)

/

(rangeavg(

((((Sum([Active Clients Low Risk])/sum([Active Clients])*(1/10))
+
(Sum([Active Clients Medium Risk])/sum([Active Clients])*(2/10))
+
(Sum([Active Clients Moderate-High Risk])/sum([Active Clients])*(3/10))
+
(Sum([Active Clients High Risk])/sum([Active Clients])*(4/10)))
-(1/10))
/((4/10-(1/10)))*100)

,0,12)

/10)<1.1,
1,

2 ))