Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
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.