topic Re: PY Set Analysis in QlikView

PY Set Analysis

Fri, 28 Nov 2014 11:04:22 GMT

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

Re: PY Set Analysis

jyothish8807 — Fri, 28 Nov 2014 11:10:18 GMT

Hi Francisco,

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

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

Regards

Re: PY Set Analysis

Fri, 28 Nov 2014 11:14:29 GMT

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

Re: PY Set Analysis

Fri, 28 Nov 2014 11:24:40 GMT

works fine:

doesn't work

Re: PY Set Analysis

v_iyyappan — Fri, 28 Nov 2014 11:35:33 GMT

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,

Re: PY Set Analysis

Fri, 28 Nov 2014 11:38:33 GMT

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

Re: PY Set Analysis

jyothish8807 — Fri, 28 Nov 2014 11:58:47 GMT

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

Re: PY Set Analysis

Fri, 28 Nov 2014 12:16:15 GMT

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

Re: PY Set Analysis

Fri, 28 Nov 2014 12:17:12 GMT

I have tried that, but it doesnt work!

Thks.

Re: PY Set Analysis

Fri, 28 Nov 2014 12:26:26 GMT

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.

Re: PY Set Analysis

Fri, 28 Nov 2014 12:28:49 GMT

OK so you do want the multiples then, should be able to get this working with concat I think, maybe just tricky with the syntax of it. Are you able to post up an example app and I can take a look

cheers

Joe

Re: Re: PY Set Analysis

Fri, 28 Nov 2014 12:45:56 GMT

Joe,

Here it is.

Note that the expession "Value_PY_Concat" I need something like this:

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

Thanks a lot,

Francisco.

Re: PY Set Analysis

Fri, 28 Nov 2014 13:04:16 GMT

Ah I see, you are using that YearMonth as a dimension also, then no the concat idea won't work, as the only possible value the expression could display, are those for it's own year.

Only way I can think to do this is in the script create a 'Prior year Sales' field, which is against the current 'YearMonth' and sum on that value instead.

i.e

Yeamonth, Sales, Priot Year Sales

201410, 100, 85

2013 10, 85, xxx

etc etc

That or look to introduce a full master calendar dimension table with the required point in time comparison flags you require

Hope that helps

Joe

Re: Re: PY Set Analysis

Fri, 28 Nov 2014 14:02:01 GMT

Thanks Joe...

I already did that para created me another problem when I have products not sold in a YearMonth, I cannot do the left join.

I also can't assing 0 to sales, because the DB will be too big!

It's possible to do something like this:

=sum(aggr(sum({<YearMonth={$(=column(3))}>}Sales), Product,Region))

to use an expression value inside the SA.

Thanks again...