Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I'm working with dates.
I have MonthYear ('MMM-YYYY').
In this example I've choose from apr-2016 to jun-2016.
I've created a function MonthDiff that gives me the number of months selected, in this case, 3.
I want to get the previous "MonthDiff" months from the date I've selected.
Example:
If I choose from apr-2016 to jun-2016, so I want previous to be jan-2016 to mar-2016.
Or if I choose from jan-2016 to jun-2016, I want previous to be jul-2015 to dez-2015.
Going back to my MonthDiff = 3
I want to do the next expression to get the value jan-2016
Date(min(addmonths(MonthYear,-$(vMonthDiff))), 'MMM-YYYY')
But this is not working, it's giving me the error "Error: Error in expression: Nested aggregation no allowed"
How can I solve this problem and achieve what I want?
Is it possible?
Thanks in advance,
Sílvia
How about this?
Date(AddMonths(Min(MonthYear),-$(vMonthDiff)), 'MMM-YYYY')
What does your $(vMonthDiff) expand to ?
The expression itself looks fine, as long as it expands to an integer.
Have you created variable there, does the variable gives the 3 at this situation. Can you please let me know the value or string you are getting got that
Hi Siliva,
exp 1 :SUM({<Year = {$(=only(Year))}, Month = {"<=$(=num(Month))"}>} [Sales Amount])
exp 2: SUM({<Year = {$(=only(Year)-1)}, Month = {"<=$(=num(Month))"}>} [Sales Amount])
if select year = 2016 month = nov
exp 1 shows sum of sales from jan to nov individually
exp2 shows previous year sum of sales from jan to nov individually
you have month year as same column
sum( monthyear = {"<=$(=(monthyear))"}[sales amount])
sum( monthyear = {"<=$(=(monthyear)-1)"}[sales amount])
Regards
Hemanth
Hi,
This is my MonthDiff Variable:
= Num(((year(max(MonthYear)) * 12) + month(max(MonthYear))) - (((year(min(MonthYear)) * 12) + month(min(MonthYear)))) + 1)
In a separated text object, is giving me the value 3
Your variable expression is being expanded to its text content and thus contains aggregation functions such as max which mean you are trying to nest aggregations which is not allowed as the error message says.
Try putting an equals sign before the variable to evaluate it to an integer
Date(min(addmonths(MonthYear,-$(=vMonthDiff))), 'MMM-YYYY')
Also have a look at this Blog post, The Little Equals Sign
Hi Bill,
I've tried what you've suggested, but is still not working.
What am I doing wrong?
Hi Siliva,
try this:
=date(min(addmonths(Disbursement_MonthYear,-$(Display2))),'MMM-YYYY')
Dispaly2 : =Num(((year(max(Disbursement_MonthYear)) * 12) + month(max(Disbursement_MonthYear))) - (((year(min(Disbursement_MonthYear)) * 12) + month(min(Disbursement_MonthYear)))) + 1)
it the above expression it shows expression ok in text box
regards
Hemanth
How about this?
Date(AddMonths(Min(MonthYear),-$(vMonthDiff)), 'MMM-YYYY')
Hi Sunny,
Thanks a lot, your suggestion works perfectly
Regards,
Sílvia