I have a problem that might be fixed by a proper IF statement. Hoping you experts might be able to help me out.
I have 3 products A B & C. During month(now) which is april, I want to forecast my sales for B&C but not A. for the past months, there is no need to forecast so you will see actual sales volume same as forecasted sales volume in the sample table.
What I have so far:
To make the espression work on front end with ease, I created a flag field in the script:- if (monthstart([Sales Month]) = monthstart(today()) and Product <> ‘A’, ‘Flag’, ‘No_Forcast’) as Flag_Field
I am using this calculated field in the expression in the bar chart. if(Flag_Field='Flag',(sum([Actual SalesVolume])/VNo_days)*VTotal_days,
but the problem is unless I select a single product, either A, B, or C the month(now) metric value does not show correct result. If I select A and B, the metric shown is the actual volume, it does not take into account the forecasted volume.
Problem defined again
when product field is not selected, Result shown = (20000+8000+3000) = 31000. Result expected =(20000+40000+15000) = 75000
when a single product is selected, for eg B, it will correctly show 40000
shout out to stalwar1 sunny you dont have to solve this if it requires hard work but just wanted to thank you for your help in the past. best.