topic Re: How to solve this using IF Statement in QlikView

How to solve this using IF Statement

Anonymous — Sun, 08 Apr 2018 02:29:38 GMT

I have a problem that might be fixed by a proper IF statement. Hoping you experts might be able to help me out.

Problem:

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,

sum([ActualSales Volume])

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.

Re: How to solve this using IF Statement

sunny_talwar — Sun, 08 Apr 2018 02:55:39 GMT

How about this

RangeSum(

Sum({<Flag_Field = {'Flag'}>}[Actual SalesVolume])/VNo_days)*VTotal_days,

Sum({<Flag_Field = {'No_Forcast'}>} ActualSales Volume]))

Re: How to solve this using IF Statement

Anonymous — Sun, 08 Apr 2018 03:38:07 GMT

Its actually a ratio that I am measuring. so in the previous formual i added /sum(Totalcost) to measure a ratio which will make the expression as below. and rangesum do not seem to work.

if(Flag_Field='Flag',((sum([Actual SalesVolume])/VNo_days)*VTotal_days)/sum(Totalcost),

sum([ActualSales Volume]/sum(Totalcost)

I left the problem to have only sum to simplify the problem, now I may have made it complex. sorry about that.

Re: How to solve this using IF Statement

sunny_talwar — Sun, 08 Apr 2018 03:48:41 GMT

May be just this

RangeSum(

Sum({<Flag_Field = {'Flag'}>}[Actual SalesVolume])/VNo_days)*VTotal_days,

Sum({<Flag_Field = {'No_Forcast'}>} ActualSales Volume]))

Sum([Totalcost])

Re: How to solve this using IF Statement

Anonymous — Sun, 08 Apr 2018 03:51:34 GMT

My apologies Sunny. you solved it the first time as always with your midas touch man. I was not thinking straight and missed a bracket Thank you so much. have a wonderful weekend.