Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am trying to use this expression in the data load editor to create a new table but I keep getting the invalid expression error.
If((1-(Sum(If(month= currentmonth, Spend))/Sum(If(month= currentmonth, Forecast)))) > .20, 'Red',
If((1-(Sum(If(month= currentmonth, Spend))/Sum(If(month= currentmonth, Forecast)))) > - .20, 'Green')) as RAG
What would need to be changed to make this able to work.
Regards,
William
Hi William,
Please share the complete load script for this expression. If you are using any aggregation function in script like Sum you need to do group by for all the fields that you are loading from the table.
BR,
Neha
Remove the space between the - and the .20
If(1 - Sum(If(month = currentmonth, Spend)) / Sum(If(month= currentmonth, Forecast)) > .20, 'Red',
If(1 - Sum(If(month = currentmonth, Spend)) / Sum(If(month = currentmonth, Forecast)) > -.20, 'Green')) as RAG
^^^
What error message you getting?
Try to write this way
If((1 - (Sum(If(month= currentmonth, Spend)) / Sum(If(month= currentmonth, Forecast)))) > 0.20, 'Red',
If((1 - (Sum(If(month= currentmonth, Spend)) / Sum(If(month= currentmonth, Forecast)))) > - 0.20, 'Green')) as RAG
I solved the issue by removing the sum from the expression.
Thanks.