6 Replies Latest reply: Jan 4, 2013 6:32 AM by jammu2004 RSS

    Need Help in Set Analysis

      Hello Friends,

       

      As I need your help in set analysis, as in the blow data I would like to show the last week Sales in straight table.

       

      The Date is organized like the follow.

       

      Year     Week  Sales

       

      2010       52      100

       

      2011       01      200

       

      2011       02      500

       

      .........

       

      .......

       

       

      2011      52       300

       

      2012      01       400 

       

       

      .....................

      .............................

       

       

       

      2012      52      209

       

      2013      01      225

       

      Regards

       

      Ammu

        • Re: Need Help in Set Analysis
          Mayil Vahanan Ramasamy

          HI

           

          Try likethis

           

           

          =sum({<Week = {'$(vmax)'}>}Sales)

           

          where vmax = Max(week)

            • Re: Need Help in Set Analysis

              Hello Mayil Vahanan.

               

              Thanks for your reply, Okay its working, now the max week is 52, minimum is 1. so for next week the year is 2013 and the week is 1, how will the expression will work.

               

              Could you please clarify...

               

              Regards

               

              Ammu

                • Re: Need Help in Set Analysis
                  Sunil Chauhan

                  use below code

                   

                  sum({<Year={$(=max(Year))},Week={$(=max({<Year={$(=Max(Year))}>}Week))}>}Sales)

                   

                  it will calculate max week on the basis of Max Year

                   

                   

                  hope this helps

                    • Re: Need Help in Set Analysis

                      Hello Luminary Sahatwar,

                       

                      Thanks for your reply, still I am unable to fix the issue, I have attached the qv.

                       

                      Could you please check and update me.

                       

                      Regards

                       

                      Ammu

                        • Re: Need Help in Set Analysis
                          Celambarasan Adhimulam

                          I have modified your script as

                          lOAD *, Year&Num(Week,'00') AS YearWeek Inline

                          [Year, Week, Sales

                          2012,          32,          434

                          2012,          33,          457

                          2012,          34,          480

                          2012,          35,          277

                          2012,          36,          251

                          2012,          37,          242

                          2012,          38,          243

                          2012,          39,          250

                          2012,          40,          228

                          2012,          41,          247

                          2012,          42,          239

                          2012,          43,          277

                          2012,          44,          298

                          2012,          45,          314

                          2012,          46,          349

                          2012,          47,          349

                          2012,          48,          358

                          2012,          49,          336

                          2012,          50,          321

                          2012,          51,          300

                          2012,          52,          306

                          2013,          1,          291

                          ];

                           

                          Then use expression as

                          If you have only one entry per week then use this

                          =FirstSortedValue({<YearWeek-={$(=Max(YearWeek))}>} Sales, -YearWeek)

                           

                          If you have more than one entry per week the use this

                          =FirstSortedValue({<YearWeek-={$(=Max(YearWeek))}>}Aggr(Sum(Sales),YearWeek), -YearWeek)

                           

                          Hope it helps

                          Celambarasan

                            • Re: Need Help in Set Analysis

                              Hello Celambarsan,

                               

                              Thanks for your reply, your expression is working in test and loading through in line.

                               

                              But when I try to retrived the data from DB, it still getting error like -. The previous expression which is working fine upto 52 Week, only the begining of 1st week I am getting such error, If I am in 2 Week, the old expression which is working fine.

                               

                              =Sum({<WEEK={"$(=max({<YEAR={"$(=max(YEAR))"}>}WEEK-1))"}>}Sales)

                               

                              can you tell me why its not working..

                               

                              Regards

                               

                              Ammu