4 Replies Latest reply: Sep 11, 2018 3:55 PM by Vu Nguyen RSS

    Weekly snapshot to Monthly report

    RAM MUTHIAH M

      I have weekly snapshot and have to show as month wise, while converting into month it is showing as 2 same months with different count. I need to show the only first row for the month of september and the rest of 2 should not be shown. Is there any solution?

       

      snapshot.JPG

       

      Output should be like below

       

      snapshot 1.JPG

        • Re: Weekly snapshot to Monthly report
          Vu Nguyen
          1. Create dimension YearMonth in your load script
          2. Create table object:
            • Dimension = YearMonth
            • Measure = Max([# Projects])

           

          Sample script

          LOAD
               *,
               Dual(Month(Day) & '-' & Year(Day), MonthEnd(Day)) as YearMonth;
          LOAD
               Date(Date#(Day,'M/D/YYYY'),'M/D/YYYY') as Day,
               [# Projects];
          LOAD * INLINE
          [
          Day, # Projects
          9/10/2018, 708
          8/31/2018, 635
          9/8/2018, 708
          9/2/2018, 638
          ];
          
            • Re: Weekly snapshot to Monthly report
              RAM MUTHIAH M

              Forgot to mention one thing is that 9/10/2018 is today's snapshot, If I include 9/11/2018 as 701, it is not working


              LOAD * INLINE 

              Day, # Projects 

              9/11/2018, 701 

              8/31/2018, 635 

              7/30/2018, 548 

              9/8/2018, 708 

              9/2/2018, 638 

              ];

               

              Still showing 708 for Sep month. It should be 701

               

              snapshot 1.JPG

               

                • Re: Weekly snapshot to Monthly report
                  Quy Nguyen

                  Why dont you create a mapping table to mark the day represents for a month, like

                   

                  MapFlag:
                  Mapping Load * INLINE [
                  Day, Flag
                  9/11/2018, 1 
                  8/31/2018, 1 
                  7/30/2018, 1 ];
                  
                  
                  A:
                  LOAD  
                       *,  
                       MonthName(Day) as YearMonth;  
                  LOAD  
                       Date(Date#(Day,'M/D/YYYY')) as Day,
                       ApplyMap('MapFlag',Day,0) As Flag,
                       [# Projects];  
                  LOAD * INLINE  
                  [ 
                  Day, # Projects 
                  9/11/2018, 701 
                  8/31/2018, 635 
                  7/30/2018, 548 
                  9/8/2018, 708 
                  9/2/2018, 638 
                  ];
                  

                   

                  Then just update your measure to : Sum({<Flag={1}>}[# Projects])

                  You can create your Mapping table manually or automatically based on your business logic.

                  • Re: Weekly snapshot to Monthly report
                    Vu Nguyen

                    Your aim is to get [# Projects] at the latest day of each YearMonth, so your table object should contain the followings:

                    • Dimension = YearMonth
                    • Measure = FirstSortedValue([# Projects],-Day)