0 Replies Latest reply: Mar 31, 2015 7:28 AM by appi karna RSS

    If Expressions,

    appi karna

      Hi experts,

       

      Need your help on the below forecast expression as it's not generating bars on the graph.

       

       

       

       

       

       

      sum(if(CalendarDate<=AddMonths(Today(),18) AND CalendarDate >= if(day(today())<=9, monthstart(Today()-30),monthstart(Today())), [Quantity Required] * If(REVENUE_FLAG = 1,[REVENUE_AMOUNT],[Material Price])))

       

      The requirement is there are 2 new fields inserted in a table (REVENUE_FLAG and REVENUE_AMOUNT) and the field REVENUE_FLAG consists of values 0 and 1 and for few of the line items where REVENUE_FLAG is 0 and 1 there's a corresponding REVENUE_AMOUNT for them. Eg out of total 30 records there's 4 records irrespective of the flag values which has REVENUE_AMOUNT.

       

       

      The need is to see all the records but in the access point the forecast revenue is to be calculated on the REVENUE_FLAG which has value = 1 and to take the corresponding REVENUE_AMOUNT.

       

       

       

      The same cannot be restricted in the extract layer or else it will fetch just the records with REVENUE_FLAG = 1 and ignore the ones with 0.

       

       

       

      Please help as it's urgent.

       

       

       

      Thanks.