Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
vikasmahajan
Esteemed Contributor

Dynamic buckets for debtors age

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

3 Replies
Partner
Partner

Re: Dynamic buckets for debtors age

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
Esteemed Contributor

Re: Dynamic buckets for debtors age

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

vishalnangare
New Contributor

Re: Dynamic buckets for debtors age

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.