0 Replies Latest reply: Mar 20, 2012 11:58 AM by w00master RSS

    Dealing with Accumulated and Non-Accumulated Data

      Hey Everyone-

       

      Really appreciate the support and help that I've received from the QV community.  I'm hoping I can get some help on this one.  I honestly don't know where to start. Attached is the QVW and sample data.

       

      Issues:

      1.  I have no control over the data source. The output is as it is

      2.  Some of the fields are accumulated data (Lifetime Stories field)  others are not (Daily New Stories)

      3.  Other fields are an sum of other fields (Weekly is based on the last seven days of Daily Data.  So, for example, on the date of 1/7/2012, the weekly field is a sum of the previous 7 days.  "Monthly" is a sum of previous 28 days)

      4.  The weekly values won't exactly match up with the sum of the daily (as well as Monthly).  The reason being because of the data set.  I cannot get into full detail on why - it would take me too long to explain).  Just go on the assumption of point #3.

       

       

      What needs to be done:

      1.  Charting and creating tables at any date dimension (Note: i didn't load in a Master Calendar)

      2.  Week over week % changes, monthly % changes.

       

       

      The problems are obviously a mixture of accumulated and non-accumulated data.  I've tried spreading out some of the data (like Lifetime Stories) and break it out by day by using in my load script:

       

      rangesum([Lifetime Stories], -previous([Lifetime Stories]) AS LifetimeDailyStories

       

      This solves some issues, but presents other problems such as:

      1.  Sometimes I do need to show the accumulated sum (from lifetime to that point in time)

      -  Full accumulation works when you show *all* the dates, but when you do a date selection it only does a full accumulation from the dates specified.

       

      2.  I thought set analysis might work, but here I run into problems with {1} in that it is using the entire data set total. I need lifetime to that specific point in time.

       

      3.  Trying to do week over week, month over month, or day over day % change is a headscratcher.

       

      I know this is a lot, but really hoping the community could provide some advice and insight.

       

      Thanks!