Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Counting the records in a period and plotting them on a line graph

Hi Community!

I have a set of data and each record has a creation date.

I want to plot a line chart which shows the number of records that have been created in either a weekly or a monthly period

I want to show the weeks/ Months across the x axis and the frequency on the y axis.

e.g.    

IDColorCreation Date
1001Red10/21/2015
1002Blue10/20/2015
1003Blue10/20/2015
1004Yellow10/19/2015
1005Red10/16/2015
1006Blue10/15/2015
1007Blue10/12/2015
1008Yellow10/11/2015
1009Red10/11/2015
1010Blue10/8/2015
1011Blue10/8/2015
1012Blue10/7/2015
1013Red10/6/2015
1014Yellow10/5/2015
1015Red

10/5/2015

What expression would I use for the Dimension and then would I need to do anything special to the Expressions to enable them to show in the correct period?

Is manipulation of the data in the script required or is there an easy way to do this?

Thanks

Tags (2)
1 Solution

Accepted Solutions
MVP
MVP

Re: Counting the records in a period and plotting them on a line graph

dimension           weekstart([Creation Date])

expression          count(ID)

1.png

4 Replies
MVP
MVP

Re: Counting the records in a period and plotting them on a line graph

dimension           weekstart([Creation Date])

expression          count(ID)

1.png

Re: Counting the records in a period and plotting them on a line graph

Hi,

for large tables you could calculate the periods in the script for performance reasons like this:

QlikCommunity_Thread_188494_Pic1.JPG

QlikCommunity_Thread_188494_Pic3.JPG

table1:

LOAD * FROM [https://community.qlik.com/thread/188494] (html, codepage is 1252, embedded labels, table is @1);

tabCalendar:

LOAD *,

    Day([Creation Date]) as Day,

    WeekDay([Creation Date]) as WeekDay,

    Week([Creation Date]) as Week,

    WeekName([Creation Date]) as WeekName,

    Month([Creation Date]) as Month,

    MonthName([Creation Date]) as MonthName,

    Dual('Q'&Ceil(Month([Creation Date])/3),Ceil(Month([Creation Date])/3)) as Quarter,

    QuarterName([Creation Date]) as QuarterName,

    Year([Creation Date]) as Year,

    WeekYear([Creation Date]) as WeekYear;  

LOAD Date(MinDate+IterNo()-1) as [Creation Date]

While MinDate+IterNo()-1 <= MaxDate;

LOAD Min([Creation Date]) as MinDate,

    Max([Creation Date]) as MaxDate

Resident table1;

hope this helps

regards

Marco

Not applicable

Re: Counting the records in a period and plotting them on a line graph

This worked perfectly, thanks for the quick response

Not applicable

Re: Counting the records in a period and plotting them on a line graph

I might look into doing it this way if the data set grows to be very large.

Thanks!

Community Browser