2 Replies Latest reply: Sep 12, 2011 11:52 AM by Dave Hewlett RSS

    Rolling delta using sets

      Hi all, thanks in advance for having a look at my problem. In a nutshell I want to chart the delta between each day of the last 7 day period and and the average of the week previous to the given day. For example:

       

      CurrentDay = CurrentDay - avg(PrevWeek)

      Sunday = Sunday - avg(PrevWeek)

      Saturday = Saturday - avg(PrevWeek)

      etc...

      the PrevWeek would dynamically calculate using an offset that I've added to the date dimension.

       

      I was trying to use a set for this but no luck. I can get the daily average value for the "previous week" just fine:

       

      ((sum({<WeekOffset={"$(=max(WeekOffset))"}>}MyMeasure))/count(distinct {<WeekOffset={'$(=max(WeekOffset)-1)'}>}DateSID))

       

      However, as soon as I try to use that value in a chart it all goes wrong. I thought that I should be able to do something as simple as:

       

      =((sum({<WeekOffset={"$(=max(WeekOffset))"}>}MyMeasure))-((sum({<WeekOffset={"$(=max(WeekOffset)-1)"}>}MyMeasure))/count(distinct {<WeekOffset={'$(=max(WeekOffset)-1)'}>}DateSID))

       

      using the daily grain of the date dimension and then restrict the chart to show only the most recent 7 entries. However, what I get is the negative daily averages for the previous week. Basically the only thing resolving is the second part of the euqation. Moreover, the chart is filtering to show only the days where WeekOffset-1 matches. I've attached a screen shot showing the results. Please note that any smuding on the chart are a result of my tears and/or rage.

       

      Keep in mind what I really want is the delta for the 7 most recent days against the average of all the numbers shown in the chart. Any advice would be greatly appreciated!

        • Rolling delta using sets
          Stefan Wühl

          Hi Dave,

           

          is is possible that you post a small QV app showing your problem here in the forum? Upload is available in the advanced editor.

           

          I think this would make it much easier to understand what is going on.

           

          Stefan

          • Rolling delta using sets

            Unfortunately for security reasons I can't share my QVW and to recreate this particular scenario is pretty complicated. For what it's worth I don't think that this kind of thing is possible as the sets always resolve according to the "current state" rather than the calculated state.

             

            I was looking for something like:

             

            (Set1Results)-(Set2Results) where sets 1 and 2 share the same dimensions. I want the chart to plot according to these results but to display based only on the dimension (date) characteristics associated with set 1. This is impossible so the results of the query end up as Set1Results (append) Set2Results.

             

            I changed the chart that I was working on to something similar but far less awesome. Thanks for the read.