topic Re: Dynamic buckets for debtors age in QlikView

Dynamic buckets for debtors age

vikasmahajan — Tue, 14 Jan 2014 10:36:54 GMT

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

Re: Dynamic buckets for debtors age

richard — Tue, 14 Jan 2014 10:45:27 GMT

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.

Re: Dynamic buckets for debtors age

vikasmahajan — Tue, 14 Jan 2014 10:51:20 GMT

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

Re: Dynamic buckets for debtors age

Anonymous — Thu, 12 Jul 2018 03:32:27 GMT

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.