Dynamic buckets for debtors age

[vikasmahajan]

As Per attachment format I have developed debtors ageing analysis report but now I want to develop dashboard like os2.jpg

and when user select  >180 above 180 list of customer's should display.

I use following expression to calculate bucket

(SUM({<FinancialYear=,Quarter=,MonthName=>} AGGR(if((Date(vDateForAgeing)-if(isnull(Date([DUE_DATE])),1,Date([DUE_DATE])) >=181),SUM({$<FinancialYear=,Quarter=,MonthName=,[Posting Date] ={"<=$(=MonthEnd(Max([Posting Date])))"}>}[Net Outstanding])),DOC_NO,Customer,Location))

/100000)

Now I want to take this expression in calculated dimension how do I do this I have try following expression in Calculated Dimes

=If( num(vDateForAgeing-Date(floor(DUE_DATE)))<=30,'Below 30',

if(num(vDateForAgeing-Date(floor([DUE_DATE])))>=31 and num(vDateForAgeing-Date(floor(DUE_DATE)))<=60,'31-60',

if(num(vDateForAgeing-Date(floor([DUE_DATE])))>=61 and num(vDateForAgeing-Date(floor(DUE_DATE)))<=90,'61-90',

if(num(vDateForAgeing-Date(floor([DUE_DATE])))>=181,'181+',

if(num(vDateForAgeing-Date(floor([DUE_DATE])))>=91 and num(vDateForAgeing-Date(floor(DUE_DATE)))<=120,'91-120',

if(num(vDateForAgeing-Date(floor([DUE_DATE])))>=121 and num(vDateForAgeing-Date(floor(DUE_DATE)))<=180,'121-180'))))))

This is giving wrong result  Please guide me how  to create chart like  os2.jpg .

Vikas

[richard]

At first I would advise you to do this in your script. Your algoritm looks good. Try using the CLASS function. You can replace the tekst you don't want with the REPLACE function.

[vikasmahajan]

Thanks Richard ,

One reason Why I am applying is dynamic ageing  when user select back date ie suppose as off '31-12-2013'  status while taking report then this report should generate from qlikview , So I pulled all open documents as well as there collections (payments along with ) for eg following data

   Inv     1031    amt  2000  dated 31/4/2013          Bal 500

   paym 1242   amt  1500  Date  1/5/2013

So Balance 500 I am calculating through AGGR function. Now I need to calculate as per dynamic bucket how to show any IDEA.

Vikas

[Anonymous]

Hi Vikas,

Use Reverse If condition. i.e. Apply Condition on Higher Range Day's First & then lower , lower, Lower.

Do not use And operator.