24 Replies Latest reply: Mar 16, 2018 4:08 AM by nagamani vadisala

count of  active months

Hi,

With below expression, we are able to get sum of value which we have in 18months. How can we get count of the months

For example, i will be having data as 100 in last 12 months, this does not mean as in all last 12 months i have data.

how can i get months and count of months in which i have data.

Can anyone please suggest on this

• Re: count of  active months

Hi,

Value={"=len(trim(Value))<>0"}

• Re: count of  active months

this is to get value  greater than zero right, how i can get months related to greater than zero value as my request

For example, i will be having data as 100 in last 12 months, this does not mean as in all last 12 months i have data.

how can i get months and count of months in which i have data.

Can anyone please suggest on this

• Re: count of  active months

in set expression ,try something like this:

months =" \$(= p( Value={"=len(trim(Value))<>0"}) months )"

• Re: count of  active months

i tried using this, but no luck.

i am using correctly as you mentioned

• Re: count of  active months

i tried using this, but no luck.

am i using correctly as you mentioned

• Re: count of  active months

the solution I gave you is when you don't have data at all on a month with the measure Value (using month as dimension).

like this, as u said, it will not count the "non active" months.

did you try it ?

• Re: count of  active months

maybe this:

Count( {< Date = {"\$(='>=' & Date(AddMonths(Max(Date), -24)) & '<=' & Date(AddMonths(Max(Date), -6)))"} >} Distinct your_month_field )

• Re: count of  active months

your_month_field = {"=Sum(Value)>0"}

• Re: count of  active months

it is not correctly written..

try like this:

Sum({<Date = {"\$(='>=' & Date(AddMonths(Max(Date), -24)) & '<=' & Date(AddMonths(Max(Date), -6)))"},Value={"=len(trim(Value))<>0"}>} Value)

• Re: count of  active months

i need to get month details youusef, i think your expression will give sum of value where value is greater than zero.

i tried below

this is giving me 1 , where we have data for particular month.

i got result as

jan    1

feb    1

apr    1

but when i trying to count it, it is giving me blank data

Can you suggest as how i can count this

• Re: count of  active months

let's try this:

Count( {< Date = {"\$(='>=' & Date(AddMonths(Max(Date), -24)) & '<=' & Date(AddMonths(Max(Date), -6)))"},

your_month_field = {"=Sum(Value)>0"} >} Distinct your_month_field )

• Re: count of  active months

hi

using this expression i am getting blank data. can you send sample app for this if possible. if i have any format issues. using that i can check

• Re: count of  active months

take a look at yellow text box expression.

i hope this helps.

Andrea

• Re: count of  active months

thank you, this is very helpful

• Re: count of  active months

look at the data loaded, at the expression used and at the result

• Re: count of  active months

HI youssef,

using app and expression you send really helpful for getting the count of data which is greater than zero.

But i need the count of months where we have only data not the sum of value where greater than zero.

I need the only count of months  when having data only

• Re: count of  active months

I send you the count of dates (same as month) when data is not null. And not count of data

• Re: count of  active months

this is thesion, you gave,

here you using sum aggr in expression, where is count of months.

Sum({<Date = {"\$(='>=' & Date(AddMonths(Max(Date), -24)) & '<=' & Date(AddMonths(Max(Date), -6)))"},Value={"=len(trim(Value))<>0"}>} Value)

• Re: count of  active months

I was talking about the app I send you. Did you open it and look at the expression used ??

• Re: count of  active months

yes,

here you using sum aggr in expression, where is count of months.

Sum({<Date = {"\$(='>=' & Date(AddMonths(Max(Date), -24)) & '<=' & Date(AddMonths(Max(Date), -6)))"},Value={"=len(trim(Value))<>0"}>} Value)

• Re: count of  active months

NO, on the app I send to you above, the expression used is this one :

=Count({<data1={"=len(trim(data1))<>0"}>}date)

• Re: count of  active months

i need to get values as

if i have active count of months in last 12 to 24 months > 6 and active count months > 6 in  last 1 to  12 months as ' R'

if i have active count of months in last 12 to 24 months =0 and active count months > 6 in  last 1 to  12 months as ' N'

if i have active count of months in last 12 to 24 months > 6 and active count months < 6 in  last 1 to  12 months as ' F'

if i have active count of months in last 12 to 24 months < 6 and active count months < 6 in  last 1 to  12 months as ' Y'

I tried using below but one condition for 'F' i am not able to get

if (count( {< Date = {"\$(='>=' & Date(AddMonths(Max(Date), -24)) & '<=' & Date(AddMonths(Max(date), -12)))"},

Monthdate = {"=count(value)>0"} >} Distinct Monthdate ) > 6,

if(count( {< Date = {"\$(='>=' & Date(AddMonths(Max(Date), -12)) & '<=' & Date(AddMonths(Max(date), 0)))"},

Monthdate = {"=count(value)>0"} >} Distinct Monthdate ) > 6,'N','R'),'Y')

Can anyone suggest here

• Re: count of  active months

Can anyone provide the suggestion on this