Count Number of Months With Sales above average per customer

[sogloqlik]

Hi.

I have the following requirement:

For every customer, I Have to count the number of months with sales above average.

The average is calculated for the sales of max year selected (Meaning if no year is selected it is 2021,) divided by the number of months selected (Jan-May = 5).

the count itself must conclude all the years.

See file with example.

The only thing which is different from the example is that sales is a very complicated expression hence it is implemented in a variable - $(Sales). I Can't touch it.

Regards,

Motty.

[sogloqlik]

AnyBody?

[eddie_wagt]

Hello,

Try this as expression. The outcome is different than your example. This expression shows the count of month where Sales are above average.

=count({$<Value={"=sum(Value)>avg(Value)"}>}Month)

Sales equal or above average

=count({$<Value={"=sum(Value)>=avg(Value)"}>}Month)

Regards Eddie

[sogloqlik]

Hi Eddie,

Thanks for your reply.
The problem is that I can't use AVG. For average I must divide the sales by the number of months... and it must be calculated only for the months of the max year...

Motty.

[eddie_wagt]

Hello Motty,

Maybe something like this then?

Set = max year and ignoring selections (meaning it will evaluate the maximum possible year in your dataset)

=count({$<Sales={"=sum({$<Year={$(=max({1}Year))}>}Sales)>sum({$<Year={$(=max({1}Year))}>}Sales)/count({$<Year={$(=max({1}Year))}>}Month)"},Year={"$(=max({1}Year))"}>}Month)

Regards

Eddie

[MayilVahanan]

Hi @sogloqlik 

try like below logic, you can alter the formula based on ur current exp.

=Sum({<Year=,Month=>}Aggr(If(Sum({<Year={$(=Max(Year))}>}Total<Customer> Sales)/Count({<Year={$(=Max(Year))}>}Total<Customer> DISTINCT Month) <= Sum({<Year=,Month=>}Sales), 1, 0), Customer,Month))

For Cust A, its 6 not 4. Because in your example, you have 1000 for 3 entries.

For Avg:

Sum({<Year={$(=Max(Year))}>}Total<Customer> Sales)/Count({<Year={$(=Max(Year))}>}Total<Customer> DISTINCT Month)

[sogloqlik]

Hi Eddie.

What is "Value" Stands for?

[sogloqlik]

Hi Mayil Vahanan.

Thank you for your reply.
Your Expression is very similar to what I've been trying to do but it doesn't work. I get 0.
I also need to give the users the possibility to choose months and years (the max years with be from the years chosen and the Avg will be calculated according to the months chosen(distinct))

Regards,
Motty

[eddie_wagt]

Sorry, it stands for "Sales". I have edit the expression in my reply.

[sogloqlik]

Thx.

I will check it out (As soon as I will get over the side effects of the COVID Vaccination)