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.