count of active months

[Anonymous]

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

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

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

[YoussefBelloum]

Hi,

add this on your set analysis:

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

[Anonymous]

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

[krishna_2644]

in set expression ,try something like this:

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

[YoussefBelloum]

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 ?

[agigliotti]

maybe this:

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

[Anonymous]

i tried using this, but no luck.

i am using correctly as you mentioned

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

[Anonymous]

i tried using this, but no luck.

am i using correctly as you mentioned

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

[agigliotti]

please add the below in set analysis:

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

[YoussefBelloum]

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)