Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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

1 Solution

Accepted Solutions
celambarasan
Not applicable

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

8 Replies
swuehl
Not applicable

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)

magavi_framsteg
Not applicable

Graph showing count of records within a range

... 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.

chandashok
Not applicable

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

Not applicable

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

celambarasan
Not applicable

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

Not applicable

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

celambarasan
Not applicable

Graph showing count of records within a range

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


Not applicable

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