topic Re: Stock Aging Buckets ? in QlikView

Stock Aging Buckets ?

Tue, 09 Sep 2014 17:41:31 GMT

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

>4Years >4Years

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

Regards,

Helen

Re: Stock Aging Buckets ?

MarcoWedel — Tue, 09 Sep 2014 17:55:12 GMT

use class function with if condition

Re: Stock Aging Buckets ?

maxgro — Tue, 09 Sep 2014 18:12:48 GMT

an useful example at

Class() with different sizes

Re: Re: Stock Aging Buckets ?

MarcoWedel — Tue, 09 Sep 2014 21:13:19 GMT

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

Re: Re: Stock Aging Buckets ?

Wed, 10 Sep 2014 17:06:33 GMT

HI Marco,

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

Help me on this...

Regards,

Helen

Re: Re: Re: Stock Aging Buckets ?

MarcoWedel — Wed, 10 Sep 2014 19:29:38 GMT

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

Re: Stock Aging Buckets ?

Thu, 11 Sep 2014 07:25:27 GMT

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

Re: Stock Aging Buckets ?

MarcoWedel — Thu, 11 Sep 2014 08:13:21 GMT

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

Thanks

Marco

Re: Stock Aging Buckets ?

MarcoWedel — Sun, 14 Sep 2014 13:13:50 GMT

please close this thread if there are no further questions

thanks

regards

Marco

Re: Stock Aging Buckets ?

Mon, 29 Sep 2014 10:56:52 GMT

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

Re: Stock Aging Buckets ?

Mon, 29 Sep 2014 11:05:39 GMT

Hope this help:

http://community.qlik.com/blogs/qlikviewdesignblog/2014/07/14/buckets

Thanks!

Re: Re: Stock Aging Buckets ?

Tue, 07 Oct 2014 07:33:12 GMT

Hi Marco,

I am getting -QTy in Some Buckets...Could you please try & let me know..

In this Link I have mentioned briefly & attached App & EXcel file also..Please let me know

http://community.qlik.com/message/625321#625321

Regards,

Helen

Re: Re: Stock Aging Buckets ?

MarcoWedel — Tue, 07 Oct 2014 08:32:44 GMT

Hi,

please close this thread if your initial question is answered and proceed this discussion in your newly created thread.

thanks

regards

Marco

Re: Stock Aging Buckets ?

Tue, 07 Oct 2014 09:08:33 GMT

HI Marco,

My Issue was not resolved requirement was chaged a little bit...

please go through this link, http://community.qlik.com/message/625321#625321

I have attached the Excel file data, Application.. I am getting Negative QTY..

Help me on this...

Thanks,

Helen