Balance availability factor - Qlik Community

nickleshkevich · ‎2018-06-12

Hi guys!

I have the following table:

Date	Material	Balance	Shipments	BAF
01.05.18	20000518	13 615,00	0,00	12,53
02.05.18	20000518	13 615,00	135,00	11,53
03.05.18	20000518	13 480,00	2 155,00	...
04.05.18	20000518	11 325,00	1 790,00	...
05.05.18	20000518	9 535,00	0,00
06.05.18	20000518	9 535,00	1 080,00
07.05.18	20000518	8 455,00	160,00
08.05.18	20000518	8 455,00	60,00
09.05.18	20000518	10 620,00	150,00
10.05.18	20000518	10 470,00	2 370,00
11.05.18	20000518	8 100,00	4 015,00
12.05.18	20000518	5 586,00	50,00
13.05.18	20000518	8 595,00	3 120,00

For each day and material, I need to calculate balance availability factor

For example for the first date (01.05.2018) it'll be:
Balance (01.05.2018 = 13 615,00) - Shipments (01.05.2018 = 0,00) - Shipments (02.05.2018 = 135,00) - .. - Shipments( i ), until result will not be negative. Something like that:

Balance*	Shipments	Result	Flag
13 615,00	0,00	13 615,00	1
13 615,00	135,00	13 480,00	1
13 480,00	2 155,00	11 325,00	1
11 325,00	1 790,00	9 535,00	1
9 535,00	0,00	9 535,00	1
9 535,00	1 080,00	8 455,00	1
8 455,00	160,00	8 295,00	1
8 295,00	60,00	8 235,00	1
8 235,00	150,00	8 085,00	1
8 085,00	2 370,00	5 715,00	1
5 715,00	4 015,00	1 700,00	1
1 700,00	50,00	1 650,00	1
1 650,00	3 120,00	- 1 470,00	0

BAF(balance availability factor) = Sum(Flag) + last_value(in our case it's = 1 650,00) / Appropriate value of Shipments (3 120,00)

BAF = 12 + 0,53 = 12,53

Could you help me, please?

Thank you very much!

Anonymous · ‎2018-06-12

Hi,

To get to the second table you showed, you can use it:

Data:

Load * Inline

[

Date, Material, Balance, Shipments

01/05/2018, 20000518, 13615, 0

02/05/2018, 20000518, 13615, 135

03/05/2018, 20000518, 13480, 2155

04/05/2018, 20000518, 11325, 1790

05/05/2018, 20000518, 9535, 0

06/05/2018, 20000518, 9535, 1080

07/05/2018, 20000518, 8455, 160

08/05/2018, 20000518, 8455, 60

09/05/2018, 20000518, 10620, 150

10/05/2018, 20000518, 10470, 2370

11/05/2018, 20000518, 8100, 4015

12/05/2018, 20000518, 5586, 50

13/05/2018, 20000518, 8595, 3120

];

FinalData:

Load If(RowNo()<3, Balance, peek(Balance) - peek(Shipments)) As Balance,

Shipments,

If(RowNo()=1, Balance, peek(Result) - Shipments) As Result,

If(If(RowNo()=1, Balance, peek(Result) - Shipments) > 0, 1, 0) As Flag

Resident Data

Order by Date;

Drop Table Data;

I am not sure how I am suposed to calculate BAF. There shoud be one BAF for each row? How did you get to the value "11,53" on the second record of your first table?

nickleshkevich · ‎2018-06-13

Hi, thank you for your answer,
I guess, I didn't explain it right, I'll try again.
I have the table with fields Material, Date and calculated fields Balance, Shipments. For each Material and Date, I need to calculate also BAF, which calculate depends on the values in the future period.
For example:
BAF from my previous for Material = '20000518' and Date = '01.05.2018' will be calculated like this:

Step 1

Balance(01.05.2018) - Shipments(01.05.2018) = 13615.00 - 0.00 = 13615.00 (Result 1), then I need to check is this value positive, if so, then BAF = BAF + 1, next step
Step 2

Balance for evening (01.05.2018) - Shipments(02.05.2018) = 13615.00 - 135.00 = 13480.00 (Result 2), is this value positive? if so, BAF = BAF + 1, next step

......

Do it until Balance positive

last step 13

We have Result 12 which is 1650.00 and Shipments for this step is 3120.00, it's greater than our Result 12, so for this step, BAF will be calculated by another formula:

BAF = BAF + Result 12 / Shipments (13.05.2018) = 12 + 1650.00 / 3120.00 = 12.53

If I could write an algorithm, it would be something like this:

BAF = 0;
Res = Balance (01.05.2018);
While Res > 0 do
     if(Res - Ship[BAF] > 0)
          BAF = BAF +1;
          Res = Res - Ship[BAF];        
     else
          BAF = BAF + Res / Ship[BAF + 1]
          Res = Res - Ship[BAF + 1];
End While

I had an idea to do it with
RangeSum( Below( Sum(Shipments), 0, i) )

but I don't know how to calculate i in frontend(table) expressions

Anonymous · ‎2018-06-13

Hello again, Nikolay!

With this script ...

Data:
Load * Inline
[
Date, Material, Balance, Shipments
01/05/2018, 20000518, 13615, 0
02/05/2018, 20000518, 13615, 135
03/05/2018, 20000518, 13480, 2155
04/05/2018, 20000518, 11325, 1790
05/05/2018, 20000518, 9535, 0
06/05/2018, 20000518, 9535, 1080
07/05/2018, 20000518, 8455, 160
08/05/2018, 20000518, 8455, 60
09/05/2018, 20000518, 10620, 150
10/05/2018, 20000518, 10470, 2370
11/05/2018, 20000518, 8100, 4015
12/05/2018, 20000518, 5586, 50
13/05/2018, 20000518, 8595, 3120
];


TmpData:
NoConcatenate
Load Date,
     Material,
     If(RowNo()<3, Balance, peek(Balance) - peek(Shipments)) As Balance,
     Shipments,
     If(RowNo()=1, Balance, peek(Result) - Shipments) As Result,
     If(If(RowNo()=1, Balance, peek(Result) - Shipments) > 0, 1, 0) As Flag
Resident Data
Order by Date;


Tmp:
Load Sum(Flag) As Factor
Resident TmpData
Group By Flag
Order By Flag Desc;


Let vSumFlag = Peek('Factor', 0, 'Tmp');
Let vFactor = Round(Peek('Balance', $(vSumFlag), 'TmpData') / Peek('Shipments', $(vSumFlag), 'TmpData'), 0.01);
Let vFirstBAF = vSumFlag + vFactor;


FinalData:
Load Date,
     Material,
     Balance,
     Shipments,
     If(Num#('$(vFirstBAF)') - RowNo() + 1 > 0, Num#('$(vFirstBAF)') - RowNo() + 1, 0) As BAF
Resident TmpData;


Drop Table Data, Tmp, TmpData;

...I end up with this table:

Is this the result you are expecting?

nickleshkevich · ‎2018-06-14

Hello, Daniel, thank you for the answer!

Unfortunately, this is not suitable for me(

I attached a piece of data for an example and Excel doc with correct values.

Thank you very much for your desire to help!

nickleshkevich · ‎2018-06-26

Need help!