cancel
Showing results for
Did you mean:
Not applicable

# Hello,

I have this expression in a pivot table to get the previous year sales:

=sum(aggr(sum({<MonthYear,YearMonth={"\$(=YearMonth-100)"}>}SalesValue)/100, Product,Region))

it works fine until I select, for example, 2 YearMonth!?

I think it's because there are values that exists in one YearMonth, but not in the other, so their is nulls in the value columns...

Any suggestions?

Regards,

Francisco

13 Replies
Master II

Hi Francisco,

remove Monthyear from your expression, then it will work fine.

=sum(aggr(sum({<YearMonth={'\$(=YearMonth-100)'}>}SalesValue)/100,product,Region))

Regards

KC

Best Regards,
KC
Not applicable
Author

!Hi,

My mistake the expression is

=sum(aggr(sum({<YearMonth={"\$(=YearMonth-100)"}>}SalesValue)/100, Product,Region))

and it doesn't work when I select more than a YearMonth!

More suggestions?

Thnks.

Not applicable
Author

works fine:

doesn't work

Specialist

Hi,

Set analysis take only one month. so you assign the  vYearMonth = Max(Yearmonth)

If you choose two month, It will take max month only.

Use expression like this

=sum(aggr(sum({<YearMonth={"\$(=Date(vYearMonth-100, 'YYYY-MMM')"}>}SalesValue)/100, Product,Region))

Note : I dont know what u used in yearmonth format, so update your format

Regards,

Not applicable
Author

Hi Francisco,

YearMonth={"\$(=YearMonth-100)"}>}

your set analysis above is trying to set a specific YearMonth, when selecting more than one you obviously have multiple options so your set expression is failing.

If you actually want two or more possible values in the set analysis, maybe look to concat your YearMonth selection and build a string of values to plug in to the set analysis.

Something like Concat(Distinct chr(39) & YearMonth & chr(39), ',') and use that in the set

hope that helps

Joe

Master II

Hi Francisco,

Since you are trying to get the previous year, your expression is filtering out only one year, so when you select you Yearmonth you are getting an error.

Try this simply replace this part:

<YearMonth={'\$(=Max(YearMonth)-100)'}>}Salesvalue

This will give you by default value for the previous Year without selecting anything.

Regards

KC

Best Regards,
KC
Not applicable
Author

Or if you are just after one year based on max selected etc, look at the other suggestions posted of course

Not applicable
Author

I have tried that, but it doesnt work!

Thks.

Not applicable
Author

Hi Joe,

No, I need to be dynamic to the diferent selected YearMonth!

I already tried (but probably did somethig wrong!) with concat.

with this expression:

=sum(aggr(sum({<YearMonth={\$(=Concat(Distinct chr(39) & YearMonth & chr(39), ','))}>}SalesValue)/100, Product,Region)

I have the exact same problem, it works fine with 1 selection, but doesn't work with multi selections...

and I also need the concat to use YearMonth-100...

Any more suggestions?

Thanks a lot,

Francisco.

Community Browser