Post reply

Summed Daily totals giving different amounts from Monthly totals

  • September 10, 2010 at 2:01 am

    #226185

    Hi all,

    We have a cube that was displaying revenue at the monthly level for our shops.

    The aggregration was done in SQL by before being read into the fact table that the cube was based on.

    I was tasked to take the aggregration down to daily level.

    What has happened is that my cubed monthly totals do not work out to the 'old' monthly sales.

    One store is 0.4 % different on one measure and a total figure is about £300 out on sales of £48K

    I am thinking a rounding error.

    I appreciate the details are important but I do not want to come on here and expect someone to look through my code. 🙂

    I am more after pointers on what to look for and if anyone has seen this sort of thing before.

    Any input appreciated.

    Thanks all.

  • September 10, 2010 at 4:36 am

    #1219067

    It's likely anything BUT a rounding error. Simple addition or SUMming doesn't produce rounding errors unless the data has something like 4 decimal places and you're only calculating with 2. It's rather likely the you've somehow left the entries in the last hours (last full day, really) off the last day of a month off (which is more than 3%). Did you use BETWEEN in a WHERE clause when you were building the aggregates? Did you use whole dates like the following?

    WHERE somecol >= MonthStartDate and someCol <= MonthEndDate

    Make sure you're including the final day if any times on the unaggregated data are present. Like this...

    WHERE somecol >= MonthStartDate and someCol [font="Arial Black"]< DATEADD(dd,1,MonthEndDate)[/font]

    Without the code, table creates, and some readily consumable data, that's about all I can come up with. 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply