4 Replies Latest reply on Aug 28, 2012 5:40 AM by Elad Lanir

    manipulating data in week level

    Elad Lanir

      i have an input data detailed at the level of date, site, product group, number of active sales.

      where first 3 are dimensions and last is a measure.

       

      for each day, the raw level holds the total number of active deals.

       

      i would like to display a total number of active deals as a trend but the trend needs to be at week level while the total number of deals is not to be aggregated for the whole week,

      for example, this is how the input looks like:

       

      datesite
      product group
      number of active deals
      1/8usxx1,000,000
      2/8usxx1,100,000
      3/8usxx1,200,000
      4/8usxx1,000,000

       

      for that input i want to display a trend by week level but a simple sum will display for this week a total of 4,300,000 which is not true.

      the correct number should be only 1,000,000 since deals are live for a few days and need not to be count twice.

       

      so, my options are to find a way to display the trend line at a day level and the axes at the week level, or to find a way to calculate the difference between two values (using "previous value"  didn't give me the desired result unless i used it wrong) or maybe at the extraction level to somehow create a calculated field that will hold the diff between to rows.

       

      any ideas?

        • 1. Re: manipulating data in week level
          Gordon Young

          Hi Elad. I want to make sure I understand your question.

           

          Is it that you want to simultaneously display

          (1)  active number of active deals during a week with a trend line

          (2)  the number of active deals for the end of a week by only showing the last day in the week?

           

          Could you give us an idea what you would want the finished visual to look like?

          • 2. Re: manipulating data in week level
            Elad Lanir

            i need the first option.

            regarding the above example,

            on the first day we have 1M, on second day we have additional 100K

            on third day we have additional 200K

            on last day 300K deals were closed so total number of active deals went down to 1M.

            the sum of active deals for the whole period is therefor 1M.

            • 3. Re: manipulating data in week level
              Tracy Rodgers

              Hi Elad,

               

              One possible idea would be to use the lookup function (if the week trend is always over 4 or 5 days--here it's only 4, so my below example may need to be adjusted) to create a calculated field and use a dual axis with Number of Active Deals. For example, the calculation may look similar to the following:

               

              if  LOOKUP(sum([# of Active Deals]) , first())=LOOKUP(sum([# of Active Deals]) , first()+3) then LOOKUP(sum([# of Active Deals]) , first()+3)

              else LOOKUP(sum([# of Active Deals]) , first()+3) -LOOKUP(sum([# of Active Deals]) , first())

              end

               

              Hope this helps a bit!

               

              -Tracy

              • 4. Re: manipulating data in week level
                Elad Lanir

                hi tracy,

                thank you but this is not what i need.

                 

                i know its a bit hard to understand but i'll try to explain again:

                 

                lets say that for a period of 30 days, each day i have 1M active deals.

                if i want to present a trend on the week level, the graph will show 7M active deals per week which is not really the result.

                 

                i think it is similar to an inventory management data:

                each day you have inventory going in and out of your store and you want to know every day what is your final stock level.

                point is that in my case, i dont know how many items went in or out of the store, i only know the "end of day" sum.

                now take that and group it on week level....

                 

                any ideas?