8 Replies Latest reply: Mar 9, 2012 7:07 AM by simonperry

# Graph showing count of records within a range

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

• ###### Graph showing count of records within a range

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)

• ###### Re: Graph showing count of records within a range

Hi,

Hope the attached file will be give better understand / idea for your requirement

Regards

Ashok

• ###### Re: Graph showing count of records within a range

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

• ###### Re: Graph showing count of records within a range

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

• ###### Re: Graph showing count of records within a range

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

• ###### Graph showing count of records within a range

Hi,

Check 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

From ... Where date(date#(right(DUEDT,6),'YYMMDD'),'DDMMYYYY') > Today();

Hope it helps

Celambarasan

• ###### Re: Graph showing count of records within a range

Hi , it doesn't like date# ?? I'd tried that one myself - along with a few others ! Regards