2 Replies Latest reply: Oct 21, 2016 5:11 PM by Heather Chamblee RSS

    Multiplication for multiple filters

    Heather Chamblee

      I am trying to multiply 2 filtered sets from 1 Item but can't figure out how to do this.

       

      My variable STATVALUE contains all the data, and my variable VARIABLEID contains the filters.  In this case I want to multiply the STATVALUE for VARIABLEID = AVERAGEHEATRATE with the STATVALUE for VARIABLEID = GENMARKETFUELPRICE. 

       

      I have tried without sucess

      avg( {$<VARIABLEID={'GENMARKETFUELPRICE'}>}STATVALUE * {$<VARIABLEID={'AVERAGEHEATRATE'}>}STATVALUE )

      avg( STATVALUE(IF(VARIABLEID='GENMARKETFUELPRICE')) * STATVALUE(IF(VARIABLEID='AVERAGEHEATRATE')) )


      These values span over dates so if I split out the average to each piece it works for the monthly level but doesn't compute like I want at the annual level.Below I have

      avg( {$<VARIABLEID={'GENMARKETFUELPRICE'}>}STATVALUE)  * avg({$<VARIABLEID={'AVERAGEHEATRATE'}>}STATVALUE )

      Capture.PNG

      When I compress to the annual level I want to see sum(GENMARKETFUELPRICE(ii)*HR(ii))

      Any help with this would be much appreciated.