Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for
Did you mean:
MVP

## 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

If the issue is solved please mark the answer with Accept as Solution & like it.
If you want to go quickly, go alone. If you want to go far, go together.
3 Replies
Partner - Creator

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.

MVP
Author

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