13 Replies Latest reply: Nov 28, 2014 9:02 AM by Francisco Cordoeiro RSS

    PY Set Analysis



      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?





        • Re: PY Set Analysis
          Jyothish KC

          Hi Francisco,


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






          • Re: PY Set Analysis

            works fine:


            doesn't work


            • Re: PY Set Analysis
              Iyyappan v



              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


              • Re: PY Set Analysis
                Joseph Simmons

                Hi Francisco,




                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


                  • Re: PY Set Analysis
                    Joseph Simmons

                    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

                        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,



                          • Re: PY Set Analysis
                            Joseph Simmons

                            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




                              • Re: Re: PY Set Analysis



                                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,



                                  • Re: PY Set Analysis
                                    Joseph Simmons

                                    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.



                                    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


                                      • Re: Re: PY Set Analysis

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