Count number of months with sales more than average

[#1]

Hello!

I have table with dimensions

Factory,ItemGroup,Item

I want to add expression to count number of months with sales more than average in month

For example item1, Sales June = 10, July = 12, August=11, September = 13

average = 11,5, the expression should give 2 (because 2 months have sales more than 11,5)

I try to write such formula, but it gives null

Sum

(

  if

  (

       aggr(Sum({<Customer={'Outer'}, SalesDate={">=$(vDateForABC)"}, Qty={">0"}>}    Qty),Year,Month,Factory,ItemGroup,Item)

       >=

       avg(aggr(Sum({<Customer={'Outer'}, SalesDate={">=$(vDateForABC)"}, Qty={">0"}>} Qty),Year,Month,Factory,ItemGroup,Item))

       ,1,0

  )

)

What could be the mistake?

[avinashelite]

Hi Alex,

Check whether the variable is getting set properly.

Can you please provide the sample data.

[#3]

Ok, without variables. the same result

Sum

(

  if

  (

       aggr(Sum(Qty),Year,Month,Factory,ItemGroup,Item)

       >=

       avg(aggr(Sum(Qty),Year,Month,Factory,ItemGroup,Item))

       ,1,0

  )

)

[avinashelite]

Can you please post your app, let me have a check

[#5]

Application contains cyrillic

[anbu1984]

Not able to open the attached qvw

[avinashelite]

Hi,

I am not able to open the document, can u upload it again.

[#8]

try this

[PrashantSangle]

Hi,

First check that below expression text object, then check in chart

avg(aggr(Sum({<Customer={'Outer'}, SalesDate={">=$(vDateForABC)"}, Qty={">0"}>} Qty),Year,Month,Factory,ItemGroup,Item))

expression gives you correct avg

if not then use Total Keyword like

avg(aggr(Sum({<Customer={'Outer'}, SalesDate={">=$(vDateForABC)"}, Qty={">0"}>} Total Qty), Year,Month,Factory,ItemGroup,Item))

Regards

[#10]

thanks, but i resolved my problem, First of all it is not correct to use nested aggregation as i used before. And yes, total is necessary