Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

New Contributor III

If Expressions,

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.


Tags (3)