Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
vikasmahajan

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

Hope this resolve your issue.
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
richard
Partner - Creator
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.

vikasmahajan
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

Hope this resolve your issue.
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.
Anonymous
Not applicable

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.