Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All , I have a file of order lines for which I have calculated the number of days between today and the due date. I'm trying to create a graph showing the count of lines between 0 -10 , 11 - 20 and over 20 days. I can't to find the correct expression to display the data. I just get 3 bars with the same total of order lines - not the sum for each range. Could someone assist please.Regards,Simon
Hi,
Here don't need to use $()
Try with this
date(date#(right(DUEDT,6),'YYMMDD'),'DDMMYYYY') as due,
if((Today() - date(date#(right(DUEDT,6),'YYMMDD'),'DDMMYYYY')) <= 10 , '0-10' ,
if((Today() - date(date#(right(DUEDT,6),'YYMMDD'),'DDMMYYYY')) > 10
and (Today() - date(date#(right(DUEDT,6),'YYMMDD'),'DDMMYYYY')) <= 20 , '11-20' ,'21-31' )) as DDate,
Or
Try like this
SET due=date(date#(right(DUEDT,6),'YYMMDD'),'DDMMYYYY');
$(due) as due,
if((Today() - $(due)) <= 10 , '0-10' ,
if((Today() - $(due)) > 10 and (Today() - $(due)) <= 20 , '11-20' ,'21-31' )) as DDate,
Hope it helps
Celambarasan
You need to use a calculated dimension in your graph, use the class() function or just a couple of if():
=if(DiffDate <=10, '0-10', if(DiffDate <=20, '11-20','over 20'))
and as expression just
=count(RecordID)
... And for better performance, do swuehl's code in your load script instead of at runtime.
Kind regards
BI Consultant
Hi,
Hope the attached file will be give better understand / idea for your requirement
Regards
Ashok
Hi thanks for the reply. Sorry for the delay , I had a production issue to deal with. I copied in your code and modified it to use a variable that was created by converting our CYYMMDD date. I substituted this for your "Date" filed using $(due) - is this correct or can I use our date format ? By adding this code it caused multiple synthetic links which points me to a syntax error. The code I'm using is as follows :-
date(date#(right(DUEDT,6),'YYMMDD'),'DDMMYYYY') as due,
if((Today() - $(due)) <= 10 , '0-10' ,
if((Today() - $(due)) > 10 and (Today() - $(due)) <= 20 , '11-20' ,'21-31' )) as DDate,
I also need need to add in another group , so it contains <0 , 0 - 10 , 11 - 20 and >20 as it includes overdue which are negative.
Regards,Simon
Hi,
Here don't need to use $()
Try with this
date(date#(right(DUEDT,6),'YYMMDD'),'DDMMYYYY') as due,
if((Today() - date(date#(right(DUEDT,6),'YYMMDD'),'DDMMYYYY')) <= 10 , '0-10' ,
if((Today() - date(date#(right(DUEDT,6),'YYMMDD'),'DDMMYYYY')) > 10
and (Today() - date(date#(right(DUEDT,6),'YYMMDD'),'DDMMYYYY')) <= 20 , '11-20' ,'21-31' )) as DDate,
Or
Try like this
SET due=date(date#(right(DUEDT,6),'YYMMDD'),'DDMMYYYY');
$(due) as due,
if((Today() - $(due)) <= 10 , '0-10' ,
if((Today() - $(due)) > 10 and (Today() - $(due)) <= 20 , '11-20' ,'21-31' )) as DDate,
Hope it helps
Celambarasan
Hi , many thanks for your help ( I'm using the top version ) . One other question , I'd like to omit past records from the load but I can't seem to get the code right to select using variables. I'd like to select due > Today but it won't recognise the fields. Can you use Today in the select ? Do you have to use $(due) ? Regards , Simon
Hi,
Check with this
Load
date(date#(right(DUEDT,6),'YYMMDD'),'DDMMYYYY') as due,
if((Today() - date(date#(right(DUEDT,6),'YYMMDD'),'DDMMYYYY')) <= 10 , '0-10' ,
if((Today() - date(date#(right(DUEDT,6),'YYMMDD'),'DDMMYYYY')) > 10
and (Today() - date(date#(right(DUEDT,6),'YYMMDD'),'DDMMYYYY')) <= 20 , '11-20' ,'21-31' )) as DDate
From ... Where date(date#(right(DUEDT,6),'YYMMDD'),'DDMMYYYY') > Today();
Hope it helps
Celambarasan
Hi , it doesn't like date# ?? I'd tried that one myself - along with a few others ! Regards