Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
use class function with if condition
an useful example at
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
HI Marco,
I have to do in Front End Pivot chart...
Help me on this...
Regards,
Helen
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
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
Just replace the Today() function and date field in my examples with your date fields.
Thanks
Marco
please close this thread if there are no further questions
thanks
regards
Marco
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