topic Re: Ageing in QlikView

Ageing

kamalqlik — Wed, 20 Feb 2013 06:49:40 GMT

Hi Friends,

I am facing a problem while Claculating the Item Ageing Logic .

I have a table

Item Code EntType Organization Code Qty Date

A5323 TFP 6 2 11/03/2011

A5323 DIS 6 3 21/07/2011

A5323 ADJ 6 4 22/07/2012

A5323 MIS 6 -2 22/08/2012

A6243 TFP 6 4 22/03/2012

A6243 MIS 6 -1 22/07/2012

Now we have to plot a chart on the basis of bucket of '0-90','90-180','180-270' and so on.

In this case we have to Find the max date on the basis of +ve Entype ie having the Positive Quantity ie TFP ,DIS etc.

For example for first Item Code A5323-TFP ,DIS ,ADJ

and For Second A6243 -TFP

and we have to Consider the max date for this +ve Entype ie

for first case

1)A5323 ADJ 6 4 22/07/2012

2)A6243 TFP 6 4 22/03/2012

and so on.

User will enter a date and from that we have to calculate the dynamic button.

Ie ...i

if((UserInputDate-max date)>0,

if((UserInputDate-max date)>='0' and (UserInputDate-max date)<='90',Dual('0-90',1), and so on for Bucket.

But we have to keep one thing in mind maxdate for calculation of Bucket will be the max date of +ve Enttype but the Quantity will be total sum

in case user has input 21/07/2012

than tha format will be

ItemCode UserInput Date Bucket MaxDate Qty

A5323 21/07/2012 0-90 21/07/2011 It will be sum of both +ve and -ve Entype as on date enter by user

A6243 21/07/2012 90-180 22/03/2012

In case anyone have idea to solve the issue.

Please guide me how to do so.

Thanks in advance

Regards

Kamal

Re: Ageing

Anonymous — Wed, 20 Feb 2013 08:00:27 GMT

See this discussion .... http://community.qlik.com/message/281656#281656?tstart=0

Jonathan

Re: Ageing

kamalqlik — Wed, 20 Feb 2013 09:20:52 GMT

hi jonbroughavone,

I want to calculate the ageing on the max date basis.

Please follow the above post fully.

regards

Kamal

Re: Ageing

Anonymous — Wed, 20 Feb 2013 11:45:32 GMT

Fair point, I didn't read you post very thoroughly.

If you want the user to use such a UserInputDate then the interval match splitting out into buckets won't help you either.

For testing the maximum date, I think you can exclude each of the preceeding buckets results in turn, intead of testing for each range. Maybe something like:

if((UserInputDate-max(Date))>270,'271 plus',

if((UserInputDate-max(Date))>180,'181 - 270',

and (UserInputDate-max date)>90,'91 - 180','0 - 90')

As for the difference between +ve and -ve Qty across the different EntType, it sounds like you need two different expressions, one with a AND Qty>0 and the other without.

Jonathan

Re: Ageing

kamalqlik — Thu, 21 Feb 2013 05:01:35 GMT

Hi Jonbroughavone,

I will try the way you are telling

KAmal