0 Replies Latest reply: Apr 19, 2013 11:46 AM by Michael Sutton RSS

    Set Analysis oddity attempting null inclusion

      So this problem has been a real head-scratcher for me. I've been getting into set analysis over the past few days, and this forum has been a huge help overcoming hurdles in this process, but I'm quite stumped on this one.

       

      I've been trying to exclude orders that have certain types of extensions marked from being averaged in a duration. When I thought this count defaulted to 0, this was working fine, but as it turns out I need to account for nulls as well, so I came up with something that looks like the following:

       

      NoExtTAT:

       

      SUM({$<[KeyDate]={">=$(=TimeStamp($(vFromDate)))<=$(=TimeStamp(DayEnd($(vToDate))))"}>

                                    *<[active]={1}>

                                    *<[nonNullField]={'*'}>

                                    *($-<[extension1]={">0"}>)

                                    *($-<[extension2]={">0"}>)

                                    }

      [duration])

      /

      COUNT({$<[KeyDate]={">=$(=TimeStamp($(vFromDate)))<=$(=TimeStamp(DayEnd($(vToDate))))"}>

                     *<[active]={1}>

                     *<[nonNullField]={'*'}>

                     *($-<[extension1]={">0"}>)

                     *($-<[extension2]={">0"}>)

                     }

      DISTINCT [orderid])

       

      Now, this works as expected as a full aggregation, and as an aggregation along many different dimensions, but there are some dimensions in my data that are, well ... disconnected from this set, seemingly. When this expression goes on a table with these dimensions, the calculation will duplicate lines as follows:

       

      Normal:

       

      orderidactiveextension1extension2NoExtTAT
      11005.2
      211--
      31--3

       

       

      With Problem Dimension:

       

      orderidareatype (problem)activeextension1extension2NoExtTAT
      1Urban100-
      1----5.2
      2Rural11--
      2-----
      3Urban1---
      3----3

       

       

      This is driving me crazy - based on the construction of the set, NoExtTAT should evaluate as null on the rows it appears on - and furthermore it appears to be ignoring some tables in the dataset. areatypeid doesn't cause it to split like this, but areatype name does - it's like it can't 'see' the dim table for areatype (only 3 rows long).

       

      Extension1, Extension2, active and nonNullField lie in the same table while areatypeid lies in a table joined to this by orderid and areatype is in a dim table joined from that. It should be noted that the following without the extension exclusions works fine at all levels I've viewed it at:

       

      allTAT

      SUM({$<[KeyDate]={">=$(=TimeStamp($(vFromDate)))<=$(=TimeStamp(DayEnd($(vToDate))))"}>

                     *<[active]={1}>

                     *<[nonNullField]={'*'}>

                     }

      [duration])

      /

      COUNT({$<[KeyDate]={">=$(=TimeStamp($(vFromDate)))<=$(=TimeStamp(DayEnd($(vToDate))))"}>

                     *<[active]={1}>

                     *<[nonNullField]={'*'}>

                     }

      DISTINCT [orderid])