Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
Try this
=sum(if (Aggr(Rank(Sum({<Year = {'$(=Max(Year)'},Month = {'$(=Max(Month)'} Sales)),Week) > 1000, 1,0))
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.
sorry Try this
=sum(if (Aggr(Sum({<Year = {'$(=Max(Year)'},Month = {'$(=Max(Month)'} Sales),Week) > 1000, 1,0))
Thanks Mohammed! You the man!