PY Set Analysis

[#1]

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

[jyothish8807]

Hi Francisco,

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

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

Regards

KC

[#3]

!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.

[#4]

works fine:

doesn't work

[v_iyyappan]

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,

[#6]

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

[jyothish8807]

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

[#8]

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

[#9]

I have tried that, but it doesnt work!

Thks.

[#10]

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.