Subtract from AddMonths with variable

[Anonymous]

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

[Anonymous]

What does your $(vMonthDiff) expand to ?

The expression itself looks fine, as long as it expands to an integer.

[Anil_Babu_Samineni]

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

[hemanthaanichet]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

Hi Bill,

I've tried what you've suggested, but is still not working.

What am I doing wrong?

[hemanthaanichet]

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

[sunny_talwar · Accepted solution]

How about this?

Date(AddMonths(Min(MonthYear),-$(vMonthDiff)), 'MMM-YYYY')

[Anonymous]

Hi Sunny,

Thanks a lot, your suggestion works perfectly

Regards,

Sílvia