3 Replies Latest reply: Nov 12, 2012 3:54 AM by Giovanni Sperti RSS

    sum/aggr no user selections time

      I have to create this columns "total value 2012", total value 2011", "total value jan 2012", "total value jan 2011" ignore user selections time

      but according dimensions country,name. How?

       

       

      LOAD * INLINE [

        

          Name, Country, Value, Time

          john, us, 10, 01-01-2012

          kate, scotland, 120, 02-02-2012

          kate, us, 1120, 03-03-2012

          mac, us, 90, 01-01-2011

          ddsds, uk, 200, 03-02-2011

          pat, scotland, 100, 03-03-2011

          pat, us, 10, 04-04-2012

         

      ];

       

      Thanks

        • Re: sum/aggr no user selections time
          Anand Chouhan

          Hi,

           

           

          Create month and year fields in the Load  script some thing like this

           

          Year(Replace(Time,'-','/')) as Year,

          Month(Replace(Time,'-','/')) as Month

           

          and

           

          Create field some thing like in the load script also

           

          sum(if(Year = 2011, Value)) as totalvalue2011,

          sum(if(Year = 2012, Value)) as totalvalue2012,

          sum(if(Year = 2012 and Month = 'Jan', Value)) as totalvaluejan2011

           

           

          HTH

          Regards,

          Anand

          • Re: sum/aggr no user selections time
            Anand Chouhan

            Hi,

             

             

            After creation of the Year and Month like

            Year(Replace(Time,'-','/')) as Year,

            Month(Replace(Time,'-','/')) as Month

             

            If you want to use Set Analysis calculation this is also can be done like

             

            For

            totalvalue2011 =  sum({<Year={'2011'}>} Value)

            totalvalue2012 =  sum({<Year={'2012'}>} Value)

            totalvaluejan2011 = sum({<Month={'Jan'}, Year={'2011'}>} Value)

             

            HTH

            Regards,
            Anand