How to calc aging stock from the end?

[MT4T]

Hi,

I have an issue with calc aging stock. I have dataset divided into 17 columns. I need to calculate from the last to first. In excel thinking should be solved like bellow (with agregated period columns). 

The issue is when I need to refer to the previous calculation. Function takes the whole formula from the previous calc. So it looks like "2xLast Formula + 3 arguments" with 12th column i met error "too many arguments". 

I have also tried another working column to get value from the last calc but it does same, copy whole last formula. 

Is there another way to deal with it?

Bellow sample with aggregated periods.

SP = SALES PLAN/DEMAND

if([QTY365+]<[SP],0,([QTY365+]-[SP])) AS [q365],
if([QTY365+]+[QTY271-360]<[SP],0,([QTY365+]+[QTY271-360]-[SP]-(if([QTY365+]<[SP],0,([QTY365+]-[SP]))))) AS [q271-360],

Bellow my 17 columns

[QTY 1- 30day],
[QTY 31- 60day],
[QTY 61- 90day],
[QTY 91-120day],
[QTY121-150day],
[QTY151-180day],
[QTY181-210day],
[QTY211-240day],
[QTY241-270day],
[QTY271-300day],
[QTY301-330day],
[QTY331-360day],
[QTY366-730day],
[QTY731-1095day],
[QTY1096-1460day],
[QTY1461-1825day],
[QTY> 1826 day],

[Mark_Little]

HI @MT4T

I am not understanding the problem or exactly what you are trying to achieve. Could you share a sample of data and what your expected outcome if.

[MT4T]

I want to calculate aging of stock based on SAP export.

Logic is I have stocks in SAP divided into seventeen time periods of aging that i have mentioned above. Sometimes the value is 0, sometimes not. I have added demand to material via "right join" key is material. So when i have some "demand" we must ignore this demand from aging. So i minus this value from last period to first (till demand remain 0).

The first approach was to do calculation via "calculated field" but the problem was referring to whole previous function (i just need result value to reffer).

With 12th calculated field i get error about too many arguments.

[QTY 1- 30day],
[QTY 31- 60day],
[QTY 61- 90day],
[QTY 91-120day],
[QTY121-150day],
[QTY151-180day],
[QTY181-210day],
[QTY211-240day],
[QTY241-270day],
[QTY271-300day],
[QTY301-330day],
[QTY331-360day],
[QTY366-730day],
[QTY731-1095day],
[QTY1096-1460day],
[QTY1461-1825day],
[QTY> 1826 day],

The second approach is do the calculation via measure in table but there is problem with loading visualizations and totals for aging periods (it works properly only in table).

IMO the first approach is better, because this calculation must be performed only one time per month (during loading of app). And i will have more space to do calc and compare data.

I have added app with structure of aging export file. Just add one column for "demand" with random values.

Bellow are first 3 "excel logic" function modified for QLIK.

if([QTY> 1826 day]+<[SP],0,([QTY> 1826 day]-[SP])) AS [q>1826],
if([QTY> 1826 day]+[QTY1461-1825day]<[SP],0,([QTY> 1826 day]+[QTY1461-1825day]-[SP]-(if([QTY> 1826 day]<[SP],0,([QTY> 1826 day]+[QTY1461-1825day]-[SP]-[q>1826]))))) AS [q1461-1825],

if([QTY> 1826 day]+[QTY1461-1825day]+[QTY1096-1460day]<[SP],0,([QTY> 1826 day]+[QTY1461-1825day]+[QTY1096-1460day]-[SP]-(if([QTY> 1826 day]+[QTY1461-1825day]+[QTY1096-1460day]<[SP],0,([QTY> 1826 day]-[SP]-[q>1826]-[q1461-1825]))))) as [q1096-1460]

....

But Qlik takes whole "if([QTY> 1826 day]+[QTY1461-1825day]+[QTY1096-1460day]<[SP],0,([QTY> 1826 day]+[QTY1461-1825day]+[QTY1096-1460day]-[SP]-(if([QTY> 1826 day]+[QTY1461-1825day]+[QTY1096-1460day]<[SP],0,([QTY> 1826 day]-[SP]-[q>1826]-[q1461-1825])))))" while he just need value of "[q1096-1460]"

[Mario_De_Felipe]

hi @MT4T,

if you want to could aggregate the 17 QTY fields, you could do manual script: Go to Load Script, Unlock the autogenerated section and edit the load of your xlsx:

[QTY 1- 30days] +
[QTY 31- 60days] +
[QTY 61- 90days] +
[QTY 91-120days] +
[QTY121-150days] +
[QTY151-180days] +
[QTY181-210days] +
[QTY211-240days] +
[QTY241-270days] +
[QTY271-300days] +
[QTY301-330days] +
[QTY331-360days] +
[QTY366-730days] +
[QTY731-1095days] +
[QTY1096-1460days] +
[QTY1461-1825days] AS [QTY AGGR],

best regards

[MT4T · Accepted solution]

A few months later i figured this out.

There is need "Load on Load" every single aging group should be calculated in his own load with loading previous load too.

With this load will take only $value of previous calc not whole expression.

Example below.

Load
if(QTY_1826_D+QTY_1461_1825_D+QTY_1096_1460_D+QTY_731_1095_D+QTY_366_730_D+QTY_331_360_D+QTY_301_330_D
<[SP],0,(QTY_1826_D+QTY_1461_1825_D+QTY_1096_1460_D+QTY_731_1095_D+QTY_366_730_D+QTY_331_360_D+QTY_301_330_D
-[SP]-AG_Q_1826_D-AG_Q_1461_1825_D-AG_Q_1096_1460_D-AG_Q_731_1095_D-AG_Q_366_730_D-AG_Q_331_360_D
)) as AG_Q_301_330_D,
*;
Load
if(QTY_1461_1825_D+QTY_1096_1460_D+QTY_731_1095_D+QTY_366_730_D+QTY_331_360_D
<[SP],0,(QTY_1461_1825_D+QTY_1096_1460_D+QTY_731_1095_D+QTY_366_730_D+QTY_331_360_D
-[SP]-AG_Q_1826_D-AG_Q_1461_1825_D-AG_Q_1096_1460_D-AG_Q_731_1095_D-AG_Q_366_730_D
)) as AG_Q_331_360_D,
*;
Load
if(QTY_1826_D+QTY_1461_1825_D+QTY_1096_1460_D+QTY_731_1095_D+QTY_366_730_D
<[SP],0,(QTY_1826_D+QTY_1461_1825_D+QTY_1096_1460_D+QTY_731_1095_D+QTY_366_730_D
-[SP]-AG_Q_1826_D-AG_Q_1461_1825_D-AG_Q_1096_1460_D-AG_Q_731_1095_D
)) as AG_Q_366_730_D,
*;
Load
if(QTY_1826_D+QTY_1461_1825_D+QTY_1096_1460_D+QTY_731_1095_D
<[SP],0,(QTY_1826_D+QTY_1461_1825_D+QTY_1096_1460_D+QTY_731_1095_D
-[SP]-AG_Q_1826_D-AG_Q_1461_1825_D-AG_Q_1096_1460_D
)) as AG_Q_731_1095_D,
*;
Load
if(QTY_1826_D+QTY_1461_1825_D+QTY_1096_1460_D
<[SP],0,(QTY_1826_D+QTY_1461_1825_D+QTY_1096_1460_D
-[SP]-AG_Q_1826_D-AG_Q_1461_1825_D
)) as AG_Q_1096_1460_D,
*;
Load
if(QTY_1826_D+QTY_1461_1825_D<[SP],0,QTY_1826_D+QTY_1461_1825_D-[SP]-AG_Q_1826_D
) as AG_Q_1461_1825_D,
*;
Load
if(QTY_1826_D<[SP],0,QTY_1826_D-[SP]
) as AG_Q_1826_D,
*;
Load
if("AMT 1- 30days" ='',round("AMT 1- 30days" / UNIT_PRICE),0) as QTY_1_30_D,
if("AMT 31- 60days" ='',round("AMT 31- 60days" / UNIT_PRICE),0) as QTY_31_60_D,
if("AMT 61- 90days" ='',round("AMT 61- 90days" / UNIT_PRICE),0) as QTY_61_90_D,
if("AMT 91-120days" ='',round("AMT 91-120days" / UNIT_PRICE),0) as QTY_91_120_D,
if("AMT121-150days" ='',round("AMT121-150days" / UNIT_PRICE),0) as QTY_121_150_D,
if("AMT151-180days" ='',round("AMT151-180days" / UNIT_PRICE),0) as QTY_151_180_D,
if("AMT181-210days" ='',round("AMT181-210days" / UNIT_PRICE),0) as QTY_181_210_D,
if("AMT211-240days" ='',round("AMT211-240days" / UNIT_PRICE),0) as QTY_211_240_D,
if("AMT241-270days" ='',round("AMT241-270days" / UNIT_PRICE),0) as QTY_241_270_D,
if("AMT271-300days" ='',round("AMT271-300days" / UNIT_PRICE),0) as QTY_271_300_D,
if("AMT301-330days" ='',round("AMT301-330days" / UNIT_PRICE),0) as QTY_301_330_D,
if("AMT331-360days" ='',round("AMT331-360days" / UNIT_PRICE),0) as QTY_331_360_D,
if("AMT366-730days" ='',round("AMT366-730days" / UNIT_PRICE),0) as QTY_366_730_D,
if("AMT731-1095days" ='',round("AMT731-1095days" / UNIT_PRICE),0) as QTY_731_1095_D,
if("AMT1096-1460days" ='',round("AMT1096-1460days" / UNIT_PRICE),0) as QTY_1096_1460_D,
if("AMT1461-1825days" ='',round("AMT1461-1825days" / UNIT_PRICE),0) as QTY_1461_1825_D,
if("AMT> 1826 days" ='',round("AMT> 1826 days" / UNIT_PRICE),0) as QTY_1826_D,
*;

LOAD
ApplyMap('SP',Material,'0') as SP,
Plant,
"Material type",
"Valuation Class",
Material AS MATERIAL,
"Material Description",
"EAN/UPC",
"Material Second Description",
"Short Model Name",
Vendors,
"Vendor Names",
"AMT 1- 30days",
"AMT 31- 60days",
"AMT 61- 90days",
"AMT 91-120days",
"AMT121-150days",
"AMT151-180days",
"AMT181-210days",
"AMT211-240days",
"AMT241-270days",
"AMT271-300days",
"AMT301-330days",
"AMT331-360days",
"AMT366-730days",
"AMT731-1095days",
"AMT1096-1460days",
"AMT1461-1825days",
"Stock < 30 days",
"Stock < 60 days",
"Stock 61-90 days",
"Stock 91-180 days",
"Stock 181-360 days",
"AMT> 1826 days",
"Total stock amount",
Currency,
"QTY 1- 30days",
"QTY 31- 60days",
"QTY 61- 90days",
"QTY 91-120days",
"QTY121-150days",
"QTY151-180days",
"QTY181-210days",
"QTY211-240days",
"QTY241-270days",
"QTY271-300days",
"QTY301-330days",
"QTY331-360days",
"QTY366-730days",
"QTY731-1095days",
"QTY1096-1460days",
"QTY1461-1825days",
"qty < 30 days",
"qty < 60 days",
"qty < 90 days",
"qty < 180 days",
"qty < 360 days",
"QTY> 1826 days",
"Total quantity",
"Base Unit of Measure",
"Total stock amount" / "Total quantity" as UNIT_PRICE
FROM [lib://DA_CONNECTORS:00_DATA_ANALYSIS/Shared Documents/To Qlick/Aging_Machine/AG_20220801(17ranges).txt]
(txt, codepage is 28591, embedded labels, delimiter is ';', msq);