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

[Anonymous]

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

=if(count({<Date = {"$(='>=' & Date(AddMonths(Max(Date), -5)) & '<=' & Date(AddMonths(Max(Date), 0)))"}>}value)>0,1,0)

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

=countif(count({<Date = {"$(='>=' & Date(AddMonths(Max(Date), -5)) & '<=' & Date(AddMonths(Max(Date), 0)))"}>}value)>0,1,0))

Can you suggest as how i can count this

[agigliotti]

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 )

[YoussefBelloum]

Please find attached.

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

[Anonymous]

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

[Anonymous]

hi

thanks for your response

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

[YoussefBelloum]

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

[Anonymous]

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)

[agigliotti]

take a look at yellow text box expression.

i hope this helps.

Andrea

[YoussefBelloum]

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

[Anonymous]

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)