Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

PY Set Analysis

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

Tags (1)
13 Replies
jyothish8807
Honored Contributor II

Re: PY Set Analysis

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

Re: PY Set Analysis

!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

Re: PY Set Analysis

works fine:

QV1.PNG.png

doesn't work

QV2.PNG.png

v_iyyappan
Valued Contributor

Re: PY Set Analysis

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

Re: PY Set Analysis

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
Honored Contributor II

Re: PY Set Analysis

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

Re: PY Set Analysis

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

Not applicable

Re: PY Set Analysis

I have tried that, but it doesnt work!

Thks.

Not applicable

Re: PY Set Analysis

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