Graph showing count of records within a range

[#1]

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

[swuehl]

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)

[magavi_framsteg]

... And for better performance, do swuehl's code in your load script instead of at runtime.

Kind regards

Magnus Åvitsland

BI Consultant

Framsteg Business Intelligence Corp.

[Anonymous]

Hi,

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

Regards

Ashok

[#5]

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

[CELAMBARASAN · Accepted solution]

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

[#7]

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

[CELAMBARASAN]

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

[#9]

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