How many weeks sales were greater than...

[felipe_oliveira]

Hi masters

I have as "date" the year, month, week and the full date.

I need to now how many weeks in the max year/month selected the sum(amount) is higher than 1,000,000

Thus, in the example above, I need to know how many weeks in October 2017 the sales was higher than 1,000,000.

Variables:

vFieldYear=max([Year])

vFieldMonth =max([Month])

The formula below works fine for the full year:

=-Sum(Aggr(Sum({<Year={$(=($(vFieldYear)))}>}amt_to_finance), Week)>1000000)

However, if I add the month to know within the selected month how many weeks were over the threshold, it does not work

=-Sum(Aggr(Sum({<Year={$(=($(vFieldYear))),Month={$(=($(vFieldMonth)))}>}amt_to_finance), Week)>1000000)

Thanks

Felipe Oliveira

[ziadm]

Try this

=sum(if (Aggr(Rank(Sum({<Year = {'$(=Max(Year)'},Month = {'$(=Max(Month)'} Sales)),Week) > 1000, 1,0))

[mdmukramali · Accepted solution]

Hi,

vFieldYear=Max(Year)

vFieldMonth=Month(Max(Date)   // when you select multiple Years it should take the Max month from Max Year  else it will take                                                             always Dec as Max Month

=Sum(if(Aggr(sum({<Year={'$(vFieldYear)'},Month={'$(vFieldMonth)'}>}amt_to_finance),Week)>1000000,1,0))

Kindly find the attached Sample application.

[ziadm]

sorry Try this

=sum(if (Aggr(Sum({<Year = {'$(=Max(Year)'},Month = {'$(=Max(Month)'} Sales),Week) > 1000, 1,0))

[felipe_oliveira]

Thanks Mohammed! You the man!