3 Replies Latest reply: Jun 16, 2011 3:24 AM by AXON SG RSS

    conditional aggr

      i have a line chart that shows the average sales over the past X number of weeks... in my current case it's 26 weeks or 182 days...

       

      one of the lines will show the overall sales while the other lines are location-specific

       

      the expression for the overall line works fine

      =aggr((calculation formula), date)

       

      however if i add

      =aggr(if(location='A', (calculation formula)), date)

      the line simply disappears

       

      in case u're wondering why im using date as the group, all records belonging to the same set are inserted at the same timing..

       

      this is the dimension of the chart

      =if(date>=Date(Today()-182) and date<=Date(Today()), workweek)

       

      so i actually have the the current/latest week at the right end of the axis followed by the past 26 weeks...

       

      please advise if nested the if-condition in the wrong way or there's a proper way around this...

        • conditional aggr
          Patrick Laredo

          hi,

           

          normally the way you would use and if() clause in a calculation is as follows:

           

          [for the example I'll say your calculation forula = sum(sales)]

           

               sum(sales)

           

          becomes

           

               sum(if(location='A', sales)).

           

          so maybe you shoudl go from

           

               aggr(sum (sales),date)

           

          to

           

               aggr(sum(if(location='A',sales)),date)

           

          does that do the trick?

            • Re: conditional aggr

              hi pat thanks for the help...

               

              this is the expression after adding in your suggestion, unfortunately it does not do the trick... the calculation goes wrong instead...

              =aggr(

              (sum(if(Site='A', field1))

              /sum(if(Site='A', field2)))*

              (sum(if(Site='A', field3))

              /sum(if(Site='A', field4)))*

              (sum(if(Site='A', field5))

              /sum(if(Site='A', field6)))

              , date)

               

              i tried using set analysis instead of aggr, it works but the graph does not adjust itself according to selections

              =(sum

              ({$<Site ={A}>} field1)

              /sum({$<Site ={A}>} field2))*

              (sum({$<Site ={A}>} field3)

              /sum({$<Site ={A}>} field4))*

              (sum({$<Site ={A}>} field5)

              /sum({$<Site ={A}>} field6))

               

              this was the original expression,

              =aggr(

              if(Site='A',

              (sum(field1)

              /sum(field2))*

              (sum(field3)

              /sum(field4))*

              (sum(field5)

              /sum(field6))

              )

              , date)

               

              oh and to add on, the date is actually part of a synthetic key that was automatically created by QV because i linked that field to the same field of a calendar table since QV's week system is different from what we want. not sure if this was part of the original problem though.

               

              with the original expression which did not appear on the chart, if i changed the date to another date field (for e.g. lastUpdated), the line actually appears but that isn't actually the group condition we want since we want to group by the record insertion date and follow our own calendar.