10 Replies Latest reply on Aug 13, 2014 2:25 PM by Noah Salvaterra Branched to a new discussion.

    Historical Data

    Amy Hartman

      I'm trying to replicate a chart in excel where I have a bar for a 2013 average...and then I'd like to have bars for the months of 2014...is there any way to group 2013 months together into one bar???

       

        • 1. Re: Historical Data
          Noah Salvaterra

          Like this (see attached)? Note, the labels could be made a bit nicer with a case statement.

           

          N.

          • 2. Re: Historical Data
            Amy Hartman

            Thanks...I'm going to fiddle and see if I can get this to work

            • 3. Re: Historical Data
              Noah Salvaterra

              Cool. Let me know if that works for you or if you get stuck. Note, I used a parameter for the year I wanted to drill down to months, but if you always wanted that to be the current year then you could use datepart('year',Today()).

               

              N.

              • 4. Re: Historical Data
                Amy Hartman

                It works great! Now I'm just trying to make the full years a monthly average...I can do it great with the sample by dividing by 12 but with my data I'm using aggregates and they don't like me.

                • 5. Re: Historical Data
                  Amy Hartman

                  Now I'm trying to take this data and create an incremental monthly goal based off of 2013 average. A year end goal of lets say 25%. The goal line would be diagonal. I can't figure out how to keep 2013 monthly average as a contact...to use as a variable since I'm using "windows" to get it. Any help would be great.

                   

                  Thanks!

                  • 6. Re: Historical Data
                    Noah Salvaterra

                    Aggregates like me fine, so I should be able to help.

                         countd(datetrunc('month',[Date]))

                    might work in place of your 12 (depending how the view and your data is structured). For the other question it would be useful to have something to work with as a sample of what you have done so far, as well as to use as a starting point for demonstrating your next steps. You could likely mock something up quickly with superstore or coffee sales that would do the job.

                     

                    N.

                    • 7. Re: Historical Data
                      Amy Hartman

                      Now I'm expanding and try to do this weekly...and for some reason using the same type of algorithm whenever a week is in between months I'm having difficulties...any ideas?

                      • 8. Re: Historical Data
                        Noah Salvaterra

                        Years and Year+Month forms a proper hierarchy, whereas Year+Month+Week or even Year+Week will not. Said differently, a given week may be split across 2 months. That is going to create some issues with your algorithm which will require some thought. i.e. would you want each part of a split week to be counted with the same weight as a full week (i.e. one that hasn't been split) or should it be weighted by the number of distinct days it contains?

                         

                        I've got some ideas on how to tackle this and I'm happy to jump help if you attach a packaged workbook that demonstrates your process so far. Superstore sales should be sufficient for a mockup assuming your data is secret. You can attach files in the advanced editor (link in the top right corner).

                         

                        Also, it is appreciated when correctly answered questions are marked as such. That closes the thread so other helpers don't get sucked in and more importantly (to me) makes me feel the time I donate here is valuable. I'm happy to extend a thread beyond an initial solution, when followup questions arise, but my ability to justify spending time relies on the knowledge that I've hit the target at some point in the past. It seems like there have already been a couple missed opportunities in that regard.

                         

                        Cheers,

                         

                        N.

                        • 9. Re: Historical Data
                          Amy Hartman

                          Thanks Noah!! That would explain A LOT. Let me think about it.

                          • 10. Re: Historical Data
                            Noah Salvaterra

                            No problem. Glad I could help (again ).

                             

                            N.