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

    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

        • Re: PY Set Analysis
          Jyothish KC

          Hi Francisco,

           

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

           

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

           

          Regards

          KC

          • Re: PY Set Analysis

            works fine:

            QV1.PNG.png

            doesn't work

            QV2.PNG.png

            • Re: PY Set Analysis
              Iyyappan v

              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
                Joseph Simmons

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

                         

                        Francisco.

                          • 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

                             

                            cheers

                            Joe

                              • Re: Re: PY Set Analysis

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

                                     

                                    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

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