Calculation possible in Script?Please help - Qlik Community

Report Inappropriate Content · ‎2014-10-24

Hi All,

I have data as show below..

LineNumber ComDate FacDate Category WhoSupply Predict

1 10-24-2014 10-24-2014 XYZ ABC 1000

2 10-24-2014 15-24-2014 XYZ ABC 1500

3 10-24-2014 17-24-2014 XYZ ABC 2000

4 10-24-2014 10-24-2014 123 ABC 1000

5 10-24-2014 15-24-2014 123 ABC 1500

6 10-24-2014 17-24-2014 123 ABC 2000

7 15-24-2014 15-24-2014 XYZ ABC 3000

8 15-24-2014 19-24-2014 XYZ ABC 1500

9 15-24-2014 24-24-2014 XYZ ABC 2000

The gap and the Line Number column is only for understanding..

Now i should have extra column "MYValue"

MyValue is the value present in Prdeict when ComDate = FacDate

So for the LineNumber 1 MYValue should be 1000 and for next few lines till ComDate = FacDate it should be 0.

Next main thing is i need ForecastedValue column:

ForecastedValue = 0 if ComDate = FacDate if not then

Predict Value of the FacDate - Predict Value of the Previous FacDate

For second line the ForecastedValue = 1500-1000

Next Column is MainValue. Difference between the MYValue

That is MainValue for ComDate = '10-24-2014' and FacDate = 15-24-2014 is 3000-1000.(Which are marked in redcolour)

So the same calculation applies for all dates.

Always we cant use Previous function because for the same ComDate we have different Categories.

So Can any one give me idea how to do this in Code?

SO finally i should have output like :

LineNumber ComDate FacDate Category WhoSupply Predict MyValue ForecastValue MainValue

1 10-24-2014 10-24-2014 XYZ ABC 1000 1000 0 0

2 10-24-2014 15-24-2014 XYZ ABC 1500 0 500 1500

3 10-24-2014 17-24-2014 XYZ ABC 2000 0 1000 X-2000

( X= Predict Value on 17-24-2014)

4 10-24-2014 10-24-2014 123 ABC 7000 7000 0

5 10-24-2014 15-24-2014 123 ABC 1500 0 -5500

6 10-24-2014 17-24-2014 123 ABC 2000 0 -5000

7 15-24-2014 15-24-2014 XYZ ABC 3000 3000 0

8 15-24-2014 19-24-2014 XYZ ABC 1500 0 -1500

9 15-24-2014 24-24-2014 XYZ ABC 2000 0 -1000

Please help.

If I am not clear on anypart let me know.

BR

Gysbert_Wassenaar · ‎2014-10-24

Your post is not clear to me. Please post a complete and correct result table. Your result table contains only MainValue values for the first three rows and X-2000 is not a value I understand. If X is also 2000 then why didn't you simply put 0 in that column?

Next Column is MainValue. Difference between the MYValue

The difference between the MYValue and what?

talk is cheap, supply exceeds demand