Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ecrobles18
Contributor III
Contributor III

How to display the week date range on the X Axis of a chart?

I am trying to replicate a report produced in excel where the chart displays the date range for the weeks of the year. For example if I wanted to show the date range for week 2 I would want to show 1/3/2016-1/9/2016 and so on. I have included a snapshot of what it is I'm trying to replicate. The expression suggested for to use is =dual(WeekStart([Date]) & ' - ' & WeekEnd([Date]),[Week Number]) however I would like to be able to script it as a part of my calendar.

1 Solution

Accepted Solutions
settu_periasamy
Master III
Master III

Hi,

I think you can use the Same function in script also.  try the example, the weekrange will be the part of your calendar.

LOAD Date,Week(Date) as Week,
dual(Date(WeekStart(Date),'MMM DD') & ' - ' & Date(WeekEnd(Date),'MMM DD YYYY'),Week(Date)) as week_range,
floor(Rand()*25000) as sales;

LOAD Date('1/1/2016'+RecNo()-1) as Date
AutoGenerate
366;

Capture.JPG

View solution in original post

8 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

Try using the Class function.

Have a look at the link.

https://community.qlik.com/docs/DOC-9195

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
settu_periasamy
Master III
Master III

Hi,

I think you can use the Same function in script also.  try the example, the weekrange will be the part of your calendar.

LOAD Date,Week(Date) as Week,
dual(Date(WeekStart(Date),'MMM DD') & ' - ' & Date(WeekEnd(Date),'MMM DD YYYY'),Week(Date)) as week_range,
floor(Rand()*25000) as sales;

LOAD Date('1/1/2016'+RecNo()-1) as Date
AutoGenerate
366;

Capture.JPG

jagan
Luminary Alumni
Luminary Alumni

HI,

Create a WeekRange column in script like below

LOAD Date,

Dual(Date(WeekStart(Date),'MMM DD') & ' - ' & Date(WeekEnd(Date),'MMM DD YYYY'), WeekStart(Date))  AS WeekRange

FROM DataSource;

Now use WeekRange  column in your chart.

Regards,

Jagan.

ecrobles18
Contributor III
Contributor III
Author

Thank you this definitely helped.

ecrobles18
Contributor III
Contributor III
Author

Thank you definitely helped.

ecrobles18
Contributor III
Contributor III
Author

Appreciate everyone's help.

MarcoWedel

Hi,

another solution to treat weeks having start and end dates in different months or years (like in your screenshot) could be:

QlikCommunity_Thread_205974_Pic1.JPG

QlikCommunity_Thread_205974_Pic2.JPG

table1:

LOAD *,

    Dual(Date(WeekStart,'MMM DD'&If(Year(WeekStart)<>Year(WeekEnd),' YYYY'))&' - ' &Date(WeekEnd,If(Month(WeekStart)<>Month(WeekEnd),'MMM ')&'DD YYYY'),WeekStart) as WeekDateRange;

LOAD *,

    Day(Date) as Day,

    WeekDay(Date) as WeekDay,

    Week(Date) as Week,

    WeekStart(Date) as WeekStart,

    WeekEnd(Date) as WeekEnd,

    WeekName(Date) as WeekName,

    Month(Date) as Month,

    MonthName(Date) as MonthName,

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

    QuarterName(Date) as QuarterName,

    Year(Date) as Year,

    WeekYear(Date) as WeekYear;

LOAD Date(MakeDate(2013)+IterNo()-1) as Date,

    Ceil(Rand()*150) as TripCounter

AutoGenerate 1

While MakeDate(2013)+IterNo()-1<=Today();

hope this helps

regards

Marco

jagan
Luminary Alumni
Luminary Alumni

Hi,

If you got the answer please close this thread by giving Correct Answer to the post which helps you.

Regards,

jagan.