Stock Aging Buckets ?

[#1]

I Need to Create Buckets for Stock Aging Report Like this....

Stock QTY   Stock Value  

0-3 Months   0-3 Months

3-6 Months   3-6 Months

6-9 Months   6-9 Months

9-12 Months  9-12 Months

1-2Years      1-2Years

2-3 Years      2-3 Years

2-3 Years    2-3 Years

>4Years      >4Years

How Can I acheive this Using Pivot Table with Some Other Dimensions& Expr...

Regards,

Helen

[MarcoWedel]

use class function with if condition

[maxgro]

an useful example at

Class() with different sizes

[MarcoWedel]

Hi,

one possible solution:

=If([Stock Age]/30.4375<12,
    Dual(SubField(Class([Stock Age],3*30.4375),' <',1)/30.4375&'-'&SubField(Class([Stock Age],3*30.4375),'< ',2)/30.4375&' Months',Class([Stock Age],3*30.4375)),
    If([Stock Age]/30.4375<=4*12,
       Dual(SubField(Class([Stock Age],12*30.4375),' <',1)/(12*30.4375)&'-'&SubField(Class([Stock Age],12*30.4375),'< ',2)/(12*30.4375)&' Years',Class([Stock Age],12*30.4375)),
       Dual('>4 Years',4*12*30.4375)     
      )
   )    

LOAD [Stock Value],
    [Stock QTY], 
    If([Stock Age]>=365.25,Dual(Num(Div([Stock Age],365.25),'#0 Years'), Floor([Stock Age]/365.25)*365.25),Dual(Num(Div([Stock Age], 30.4375),'#0 Months'),Floor([Stock Age]/30.4375)*30.4375)) as [Stock Age];
LOAD *, Today()-[Stock Date] as [Stock Age];
LOAD Timestamp(Today()-Rand()*3000) as [Stock Date],
    Ceil(Rand()*100) as [Stock QTY],
    Money(Ceil(Rand()*1000)) as [Stock Value]
AutoGenerate 200;

hope this helps

regards

Marco

[#5]

HI Marco,

I have to do in Front End Pivot chart...

Help me on this...

Regards,

Helen

[MarcoWedel]

Hi,

I removed the precalculation of stock ages from the script and only left the generation of random test data for date, QTY and value (just a guess, since you didn't provide your data model):

hope this helps

regards

Marco

[#7]

Hi Marco,

I am Having two Dates...

Purchase Date(Max(GRN_Date))    & Sales Date(Max(Invoice_Date))...

How much time the particular Item was in Warehouse In between the Purchase date & Invoice Date, as per that i have to create buckets..

Thanks,

Helen

[MarcoWedel]

Just replace the Today() function and date field in my examples with your date fields.

Thanks

Marco

[MarcoWedel]

please close this thread if there are no further questions

thanks

regards

Marco

[#10]

Hi Marco,

Thanks for your help ...

I am having the fields,

ITEM_CODE,

  QTY,

    Cost,

     SL_Date,

   SL_RCVD_ISSD  (Flag is there for Purchased & Issued ( R,I ))

I Need to get the Total quantity on basis of (Recived- Issued)

Item Cost is on the Basis of Last Date Which have purchased recently ?

Value is Total QTY * Cost

Based on this Date create buckets & Total quantiy should be bifurcated among the above Aging concept...?

Regards,

Helen